ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Differences Between Rowid & Primary Key Materialized Views

Differences Between Rowid & Primary Key Materialized Views

原创 Linux操作系统 作者:NinGoo 时间:2019-05-16 08:27:05 0 删除 编辑

Doc ID: Note:254593.1 Type: BULLETIN
Last Revision Date: 18-JAN-2005 Status: PUBLISHED


Differences Between Rowid & Primary Key Materialized Views
==========================================================

PURPOSE
=======

The purpose of this article is to explain the differences between rowid and
primary key materialized views and materialized view logs.
This article also discusses when to use rowid or primary key materialized
views/logs in a distributed environment or a data warehouse environment.

This note does not cover different types of materialized views in a
datawarehouse environment, please refer to Oracle9i Data Warehousing Guide
Release 2.

The terms snapshot and materialized view are synonymous. This article will
use the term "materialized view".

Additional notes are referenced in this article that address specific issues
or provide additional information.

SCOPE & APPLICATION
=====================

To be used by anyone interested in understanding the differences between rowid
and primary key materialized views in distributed and datawarehousing
environments.

CONTENTS
========

1- Introduction
2- Materialized View Log
2-1 Description
2-2 Using rowid materialized view log in distributed environment
2-3 Using rowid materialized view log in summary management
2-4 Using primary key materialized view log
2-5 Using primary key and rowid materialized view log
3- Materialized Views
3-1 Types of Materialized Views
3-2 Materialized views in a distributed environment
3-3 Primary key materialized view in distributed environment
3-4 Rowid materialized view in distributed environment
4- Converting a rowid materialized view to primary key materialized view
4-1 Converting a rowid materialized view to primary key materialized view
4-2 Avoiding complete refresh on Oracle8i rowid to primary key conversion
4-3 Avoiding complete refresh on Oracle7 rowid to Oracle8i primary key
conversion


1- Introduction
===============

Materialized views are schema objects that can be used to summarize, compute,
replicate, and distribute data. A materialized view stores data that has been
obtained from a query, this data remains until next refresh on the materialized
view occurs. They are suitable for distributed and data warehousing
environments.

In distributed environments, materialized views are used to replicate data at
distributed sites and synchronize updates done at several sites. The
materialized views as replicas provide local access to data that otherwise has
to be accessed from remote sites.

In data warehouses, since Oracle 8i, materialized views are used to compute and
store aggregated data such as sums and averages. Materialized views in these
environments are typically referred to as summaries because they store
summarized data. Only a few restrictions limit what can be specified. Any
number of tables can be joined together. However, they cannot be remote tables
if you wish to take advantage of query rewrite feature.

When a materialized view is created, the information stored is up to date to
the moment that it is created, but to keep it up to date we need to refresh
the materialized view regularly.

There are different methods to refresh a materialized view, and it should be
selected at materialized view creation time. One of these methods is called
FAST REFRESH. In summary, this mechanism will refresh only those rows that
have been modified since last refresh or since materialized view creation.
This mechanism is able to know which rows have experienced any modification
because we are going to record modified rows in a object called materialized
view log

For additional information describing materialized view types and refresh
mechanisms and methods, please refer to . <br /><br /><br />2- Materialized View Log <br />========================<br /> <br />2-1 Description<br />---------------<br /> <br />When a materialized view log is created on a master table, Oracle creates a <br />table named mlog$_<master_table_name>. This table is the materialized view log.<br /><br />Oracle uses this table to store a record of each change made to the master <br />table. When a materialized view that is mastered by that master table is <br />refreshed in fast mode, it queries the change records stored in the <br />materialized view log to determine which rows to pull from the master table <br />and replace in the materialized view. <br /><br />Each change record uses a unique identifier to indicate for which row in<br />the master table the change was made. This unique identifier is either rowid<br />or primary Key. Rowid will use the rowid of the record in the master table to <br />locate the changed row. Primary key will use the primary key of the record to<br />locate the changed row. <br /><br />Either, or both can be specified when the materialized view log is created. If<br />neither is specified, it will default to primary key in Oracle8/9i. If both <br />are specified, it only means that the materialized view log will capture both <br />the rowid and the primary key values for the changed row. The actual value <br />used as the unique identifier during the refresh depends on which was specified<br />at the creation of the actual materialized view.<br /><br />In order to find out what materialized view type(s) are supported by a <br />materialized view log, use view dba_mview_logs:<br /><br /> select log_owner, master, log_table, rowids, primary_key<br /> from dba_mview_logs;<br /> <br /> LOG_OWNER MASTER LOG_TABLE ROWIDS PRIMARY_KEY<br /> --------------- ---------- ----------- --------- -----------<br /> SCOTT DEPT MLOG$_DEPT NO YES<br /> <br /><br />2-2 Using rowid materialized view log in distributed environment<br />---------------------------------------------------------------- <br /><br />In Oracle7 only rowid materialized view logs are available, and since Oracle8<br />in distributed environments rowid materialized view logs exist for backward<br />compatibility with Oracle7, this is having materialized view sites in release <br />Oracle7.<br /><br />In Oracle7, the rowid materialized view log structure is as follows:<br /><br /> SQL>create table M1 (C1 number(4) , dname varchar(14));<br /> SQL>create materialized view log on M1;<br /> SQL>desc mlog$_M1<br /> Column Name Null? Type<br /> ------------------------------ -------- ----<br /> M_ROW$$ VARCHAR2(18)<br /> SNAPTIME$$ DATE<br /> DMLTYPE$$ VARCHAR2(1)<br /> <br />The rowid of the new/modified row is stored in materialized view log M_ROW$$<br />column.<br /> <br />In Oracle8/Oracle9i, we can also use rowid materialized view log if the master <br />table does not contain a Primary key. As the default in oracle8/oracle9i for <br />materialized view log is primary key, creating materialized view log without <br />the WITH ROWID option on a master table without a primary key will result <br />ora-12014:<br /> <br /> SQL> create table M1 (C1 number(4) , dname varchar(14));<br /> SQL>create materialized view log on M1; <br /> ERROR at line 1:<br /> ORA-12014: table 'M1' does not contain a primary key constraint<br /> <br />In Oracle8/Oracle9i, the rowid materialized view log structure is as follows: <br /><br /> SQL>create materialized view log on M1 with ROWID;<br /> SQL> desc mlog$_M1<br /> Column Name Null? Type<br /> ------------------------------ -------- ----<br /> M_ROW$$ VARCHAR2(255)<br /> SNAPTIME$$ DATE<br /> DMLTYPE$$ VARCHAR2(1)<br /> OLD_NEW$$ VARCHAR2(1)<br /> CHANGE_VECTOR$$ RAW(255)<br /><br />If master table is in Oracle8/Oracle9i and materialized view site in Oracle7,<br />you have to create materialized view log with rowid option for fast refresh.<br /><br /> <br />2-3 Using rowid materialized view log in summary management <br />----------------------------------------------------------- <br /> <br />Rowid materialized view logs in data warehouse/summary management are used<br />to support fast refresh of join view (MJV), aggregate (MAV), and nested <br />materialized views; this is the only materialized view log type supported<br />in this environment. These materialized view logs are often created with <br />filter columns. These columns are additional, non-primary key columns for <br />which the materialized view log change record captures values.<br /><br />In relation to materialized view logs, when creating a materialized view in a<br />Datawarehouse environment and the materialized view contains aggregates with a<br />single table, a materialized view log must contain all columns referenced<br />in the materialized view and must have been created with the INCLUDING NEW VALUES clause:<br /><br /> CREATE TABLE FACT (store_key number(8), prod_key number(6) <br /> , time_key number(8), dollar_sales number(12));<br /> <br /> If columns store_key , time_key and dollar_sales have to be referenced in the<br /> materialized view:<br /> <br /> CREATE MATERIALIZED VIEW log on FACT <br /> with rowid (store_key,time_key,dollar_sales) <br /> including new values; <br /> <br /> <br />2-4 Using Primary key materialized view log <br />-------------------------------------------<br /><br />When creating a primary key materialized view log, you must have a valid <br />primary key on master table. In case you try to create a primary key<br />materialized view when master table do not have primary key, error ORA-12014<br />will be raised.<br /><br /> A materialized view log structure is as follow:<br /><br /> Name Null? Type<br /> ----------------------------------------- -------- ---------------<br /> EMPNO NUMBER(4) <br /> SNAPTIME$$ DATE<br /> DMLTYPE$$ VARCHAR2(1)<br /> OLD_NEW$$ VARCHAR2(1)<br /> CHANGE_VECTOR$$ RAW(255)<br /> <br />EMPNO is the primary of the master table. <br /><br />Since Oracle8 Primary key is the default for materialized view logs. In <br />replication environments it should be used unless any of the exceptions <br />signalled at "Using rowid materialized view log in distributed environment"<br />section. <br /><br />Primary key materialized view log do have applications on a summary management<br />environments.<br /><br /><br />2-5 Using Primary key and rowid materialized view log<br />-----------------------------------------------------<br /><br />We can create materialized view log with both primary key and rowid<br /> <br /> SQL>create materialized view log on dept with rowid,primary key;<br /> SQL> desc mlog$_dept;<br /> <br /> Column Name Null? Type<br /> ------------------------------ -------- ----<br /> DEPTNO NUMBER(2)<br /> M_ROW$$ VARCHAR2(255)<br /> SNAPTIME$$ DATE<br /> DMLTYPE$$ VARCHAR2(1)<br /> OLD_NEW$$ VARCHAR2(1)<br /> CHANGE_VECTOR$$ RAW(255)<br /><br />We can see that both rowid and primary key of new/modified/deleted row will<br />be recorded. This configuration may be used when we have materialized view<br />site in different version ( Oracle7 and Oracle8+). <br /><br />3- Materialized Views<br />=====================<br /><br />3-1 Types of Materialized Views<br />-------------------------------<br /><br />There are different ways to distinguish one type of materialized views from <br />another. One of this classification is based on the environment where the <br />materialized view is involved and there we can distinguish different types:<br /><br />- Distributed environments<br /> <br /> * Primary Key: The materialized view includes the columns that conform the<br /> master table primary key. The materialized view will identify<br /> rows modified at master site by their primary key.<br /> <br /> * Rowid : The materialized view includes a column that will stores <br /> master table rowid. The materialized view will identify rows<br /> modified at master site by their primary key.<br /><br />- Datawarehousing environment<br /><br /> * Materialized Views with Aggregates (Aggregate): The columns that conforms<br /> the query used to create the materialized view contains <br /> aggregate.<br /> <br /> * Materialized Views Containing Only Joins (Join View): The query used to <br /> create the query is a join used to precalculate costs joins.<br /> <br /> * Nested Materialized Views (Complex) : A materialized view whose definition<br /> is based on another materialized view.<br /> <br />This note does not cover different types of materialized views in a <br />datawarehouse environment, please refer to Oracle9i Data Warehousing Guide <br />Release 2.<br /><br />To check what type of Materialized view we are using we can query to<br />dba_snapshots view:<br /><br /> select owner, name, refresh_method from dba_snapshots;<br /> <br />This classification could be applied to materialized views but it is not <br />applicable to materialized view logs.<br /><br /><br />3-2 Materialized views in a distributed environment<br />---------------------------------------------------<br /><br />When a materialized view is created, several additional mechanisms are created<br />at the materialized view site to support the materialized view. Specifically,<br />a base table, at least one index, and possibly a view are created. If you <br />create an updatable materialized view, then an internal trigger and a local log<br />(the updatable materialized view log) are also created at the materialized view<br />site.<br /><br />For Oracle8i and higher a base table and a materialized view objects are going <br />to be created. These objects are going to have the same name, but it won't be <br />possible to access directly to the base table.<br /><br />Example:<br /><br /> SQL> DROP TABLE S_DEPT;<br /> DROP TABLE S_DEPT<br /> *<br /> ERROR at line 1:<br /> ORA-12083: must use DROP MATERIALIZED VIEW to drop "SCOTT"."S_DEPT"<br /><br /> to drop this objects we will have to drop the materialized view.<br /> <br />In addition to this an index called I_SNAP$_<materialized_view_name> will be <br />created for base table.<br /><br />For releases lower than Oracle8i a base table is created with the name <br />SNAP$_<materialized_view_name> and a view with the name of the materialized<br />view on top of this table is created. Also the index <br />I_SNAP$_<materialized_view_name> is going to be created if the materialized <br />view is a primary key materialized view.<br /><br />If an updatable materialized view is created, an updatable materialized view <br />log (USLOG$_<materialized_view_name>) is used to determine which rows must be<br />overwritten or removed from a materialized view during a fast refresh. <br />A read-only materialized view does not create this log, and Oracle does not<br />use this log during a complete refresh because, in this case, the entire <br />materialized view is replaced.<br /><br /> <br />3-3 Primary key materialized view in distributed environment<br />------------------------------------------------------------<br /><br />This type of materialized views appeared for first time in release 8.0 and they<br />became the default type. To create a primary key materialized view, a primary <br />key must exist on the master (source) tables, and the materialized view log <br />must be created with primary key (default value). <br /><br />For a fast refresh of a primary key materialized view, it is required that <br />the materialized view log contains the primary key of the master table. <br />In case the materialized view log does not contain primary key information<br />an error ORA-12031 will be raised.<br /><br />Also, in case we are trying to create a materialized view primary key <br />based on an Oracle7 master table, it is possible to get an error ORA-12028<br />at creation time. This is because the Oracle7 does Oracle7 knows nothing <br />about primary key feature. This is the usual way to get an error ORA-12028<br />BUT There are also some unusual ways to get it, where basic troubleshooting<br />won't help:<br /><br /> - </font><a href="http://ningoo.itpub.net/metalink/plsql/showdoc?db=Bug&id=703287"><font face="Courier" size="2">Bug 703287</font></a><font face="Courier" size="2">, Schema Manager (Oracle Enterprise Manager) does not support<br /> WITH option. Fixed in Schema Manager 2.1.0.<br /> - Having GLOBAL_NAMES = TRUE more than once in the init.ora has caused<br /> ORA-12028 and ORA-2067 errors.<br /> - Forcing illegal global name for the database by directly updating data<br /> dictionary table instead of using ALTER DATABASE RENAME GLOBAL NAME...<br /> command can cause ORA-12028. Note that the ONLY supported way to change<br /> the global name is the ALTER DATABASE command above.<br /> - Invalid DBMS_UTILITY package can cause ORA-12028. Ensure that this package<br /> is valid.<br /> - </font><a href="http://ningoo.itpub.net/metalink/plsql/showdoc?db=NOT&id=1059092.6&blackframe=1"><font face="Courier" size="2">Note 1059092.6</font></a><font face="Courier" size="2">, ORA-23346 DURING GENERATE_REPLICATION_SUPPORT<br />If we pay attention to the description of the objects created by the <br />materialized, we will see:<br /><br />For the base table:<br /><br /> SQL> desc s_dept<br /> Name Null? Type<br /> ----------------------------------------- -------- -------------<br /> DEPTNO NOT NULL NUMBER(2)<br /> DNAME VARCHAR2(14)<br /> LOC VARCHAR2(13)<br /> <br />For USLOG$ table in case the materialized view is updatable:<br /><br /> SQL> desc uslog$_s_dept;<br /> Name Null? Type<br /> ----------------------------------------- -------- ------------<br /> DEPTNO NUMBER(2)<br /> SNAPTIME$$ DATE<br /> DMLTYPE$$ VARCHAR2(1)<br /> OLD_NEW$$ VARCHAR2(1) <br /><br />And the index I_SNAP$_S_DEPT is going to be defined on primary key columns<br />(DEPTNO) of master table.<br /><br />The main benefit of primary key materialized views is that they allow the reorganization<br />of the master tables without requiring a complete refresh from master tables after the reorganization,<br />unlike the rowid materialized view. This is because the primary key record <br />identifier does not change during master table reorganization, where as the rowid <br />does. An example of master table reorganization with a primary key <br />materialized view follows:<br /><br />1) This test is done with the scott.dept table <br /> <br /> connect scott/tiger<br /> <br />2) If the dept table do not have primary key, add it using the following command: <br /> <br /> alter table dept add constraint pk_dept primary key(deptno);<br /> <br />3) As a primary key exists on master table (dept), default materialized view log is <br /> created with primary key. In case a primary key does not exist on the master table<br /> an error ORA-12014 will be raised. <br /> <br /> create materialized view log on dept;<br /> <br />4) Creation of materialized view ( default is primary key):<br /> ORA-23415 will be raised in case the materialized view log do not record the<br /> primary key<br /> <br /><br /> create materialized view s_dept refresh fast as select * from dept;<br /> <br />5) Perform insert in materialized view and commit it:<br /> <br /> insert into dept (deptno) values (49);<br /> commit;<br /> <br /> If you select from the materialized view log you should see a row count of 1. <br /><br /> select count(*) from mlog$_dept; <br /> 1 row selected <br /><br />6) Now we want to perform a reorganization of the master table <br /> The below procedure will devalidate internal triggers which populates <br /> the materialized view log when modification are done on the master table:<br /> <br /> execute DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION('SCOTT','DEPT');<br /> <br />7) Now export the dept table: <br /> <br /> exp scott/tiger tables=dept <br /> <br /><br />8) Truncate the table to prepare it for import.<br /><br /> truncate table dept; <br /> <br />9) Import table dept.<br /><br /> imp scott/tiger tables=dept ignore=y<br /> <br />10) Turn on internals triggers.<br /><br /> execute DBMS_MVIEW.END_TABLE_REORGANIZATION('SCOTT','DEPT');<br /> <br />11) Refresh the materialized view, default is refresh method indicated in DBA_MVIEWS.<br /> <br /> execute dbms_mview.refresh('S_DEPT','f');<br /><br />12) If the materialized view is a rowid materialized view, then the error <br /> ORA-12034 will appear at refresh time, and the materialized view will need<br /> a complete refresh.<br /><br /> execute dbms_mview.refresh('S_DEPT','c');<br /> <br /><br />3-4 Rowid materialized view in distributed environment<br />------------------------------------------------------<br /><br />In Oracle7 the materialized views are always rowid materialized views, primary<br />key materialized views do not exist. That's why rowid materialized views <br />provide compatibility with master tables in releases of Oracle prior to 8.0.<br />You can also use rowid materialized views if the materialized view does not <br />include all primary key columns of the master tables or master table does not <br />contain a primary key.<br /><br />For a fast refresh of a rowid materialized view, it is required that the <br />materialized view log contains the rowids of the master table rows. In case<br />the materialized view log does not contain rowid information an error ORA-12032<br />will be raised.<br /><br />If we pay attention to the description of the objects created by the <br />materialized, we will see:<br /><br />For the base table in releases Oracle8i and higher:<br /><br />SQL> desc s_dept<br /> Name Null? Type<br /> ----------------------------------------- -------- -------------<br /> DEPTNO NOT NULL NUMBER(2)<br /> DNAME VARCHAR2(14)<br /> LOC VARCHAR2(13)<br /> <br />For the base table in releases Oracle8 and lower: <br /><br />SQL> desc snap$_s_dept<br /> Name Null? Type<br /> ----------------------------------------- -------- -------------<br /> M_ROW$$ VARCHAR2(255) <br /> DEPTNO NOT NULL NUMBER(2)<br /> DNAME VARCHAR2(14)<br /> LOC VARCHAR2(13)<br /><br />For USLOG$ table in case the materialized view is updatable:<br /><br />SQL> desc uslog$_s_dept;<br /> Name Null? Type<br /> ----------------------------------------- -------- --------------<br /> M_ROW$$ VARCHAR2(255)<br /> SNAPTIME$$ DATE<br /> DMLTYPE$$ VARCHAR2(1)<br /> OLD_NEW$$ VARCHAR2(1)<br /> <br />And the index I_SNAP$_S_DEPT is going to be defined on column M_ROW$$, <br />regardless release that we are working with.<br /><br />As explained before, rowid materialized views are not eligible for fast refresh<br />after a master table reorganization until a complete refresh has been performed.<br />If you try to refresh fast a rowid materialized view after a table <br />reorganization, you'll get an error ORA-12034.<br /><br />On distributed environments, rowid materialized views must be based on a single<br />table and cannot contain any of the following:<br /><br /> - Distinct or aggregate functions<br /> - GROUP BY or CONNECT BY clauses<br /> - Subqueries<br /> - Joins<br /> - Set operations<br /><br /><br />4. Converting a rowid materialized view to primary key materialized view<br />=========================================================================<br /><br />The majority of customers perform an incremental upgrade of their distributed<br />environments from Oracle7 to Oracle8 and above. Once the incremental upgrade <br />is complete Oracle recommends customers switch from rowid to primary key <br />materialized views, to take advantage of the performance and maintenance <br />improvements they provide.<br /><br />It is possible to convert a rowid materialized views to primary key <br />materialized view without performing a complete refresh in oracle8 and higher. <br /><br />4-1 Converting a rowid materialized view to primary key materialized view<br />-------------------------------------------------------------------------<br /><br />Converting a rowid materialized view to primary key materialized view, requires<br />you to drop and recreate the materialized view log and initially to perform a <br />COMPLETE refresh on the materialized view site before being able to perform <br />fast refreshes.<br /><br /> a. On the master site, drop the existing rowid materialized view log and <br /> recreate it so it tracks by primary key:<br /><br /> SQL> drop materialized view log on dept;<br /> SQL> create materialized view log on dept with primary key;<br /><br /> b. On the materialized view site, convert materialized view and perform a <br /> complete refresh:<br /> <br /> SQL> alter materialized view s_dept refresh fast with primary key;<br /> SQL> execute dbms_mview.refresh('s_dept','c');<br /><br /> c. From now onwards, you will be able to perform fast refreshes:<br /><br /> SQL> execute dbms_mview.refresh('s_dept','f');<br /><br />4-2. Avoiding complete refresh on Oracle8i rowid to primary key conversion<br />--------------------------------------------------------------------------<br /><br />The steps described in this section can only be followed if the master site<br />and materialized view sites involved are already running Oracle8 or<br />above. This procedure is not applicable if the materialized view has been<br />upgraded from Oracle7.<br /><br /> a. First, on the master site, alter the materialized view log to track by <br /> primary key.<br /><br /> SQL> alter materialized view log on dept add primary key;<br /><br /> b. Next, at each materialized view site, perform a fast refresh of the <br /> materialized view.<br /> <br /> Note that this step is necessary to guarantee that a fast refresh will<br /> continue to work AFTER the materialized view is altered. If this step is <br /> not run, ORA-12034 will be returned the next time that a fast refresh is <br /> executed for the snapshot and a complete refresh will be necessary to <br /> keep on refreshing this materialized view. See the end of this section<br /> for more details.<br /><br /> SQL> execute dbms_mview.refresh ('dept','f');<br /> <br /> c. Alter the materialized view to refresh fast with Primary Key.<br /><br /> SQL> alter materialized view dept refresh fast with primary key;<br /><br /> d. After altering the materialized view, you will continue to be able to perform<br /> fast refreshes:<br /><br /> SQL> execute dbms_mview.refresh('dept','f');<br /><br />Note that now the materialized view log is tracking both rowid and primary key. <br />Tracking both will increase the size of the materialized view log. If you do not <br />have materialized views that are fast refreshing with Rowid then you should <br />consider tracking only the primary key. <br /><br /><br />4-3 Avoiding complete refresh on Oracle7 rowid to Oracle8i primary key conversion<br />----------------------------------------------------------------------------------<br /><br />This section is intended for use as a guide to avoiding a complete refresh<br />after migrating Oracle7 read only materialized view environments to Oracle8i or<br />above. It uses the new Oracle8i clause 'on prebuilt table' with the 'create<br />materialized view' command. These steps are only appropriate if ALL <br />materialized view of the affected table are being converted.<br /><br />The on prebuilt table clause lets you register an existing table as a<br />preinitialized materialized view. This is particularly useful for registering <br />large materialized views. The table must have the same name and be in the same<br />schema as the resulting materialized view. The resulting materialized view must<br />be a primary key materialized view, if you try to create a rowid materialized <br />view an error ORA-12058 will be raised.<br /><br />Below there are a set of steps that show how you can convert Oracle7 read only<br />rowid materialized view to 8i primary key materialized view without having to <br />perform a complete refresh.<br /><br />This method will be useful in situations where the materialized views are very <br />large or a slow network link exists between the master and the materialized <br />view site.<br /><br />*** Please Note that the procedure detailed here is not formally documented<br />or supported. It has been tested internally, however, and works as documented.<br />It is recommended that the steps are performed in a test environment before<br />relying on them.<br /><br /> a. Migrate the databases involved to Oracle8i using the documented migration<br /> methods. Once the migration is complete use the following steps to convert<br /> the read only materialized view.<br /><br /> NOTE: make sure no changes are made to the master environment during<br /> this process.<br /><br /> b. On the master site, drop all materialized view logs and recreate them so <br /> that they are primary key based.<br /> <br /> SQL> drop materialized view log on dept; <br /> SQL> create snapshot log on dept;<br /><br /> c. On the materialized view site perform the following:<br /><br /> - Drop all the views associated with the materialized views:<br /><br /> SQL> drop view dept;<br /><br /> - Rename all SNAP$_<materialized view_name> tables to <br /> <materialized view_name>:<br /><br /> SQL> rename snap$_dept to dept;<br /><br /> - Drop the M_ROW$$ column from each of these tables:<br /><br /> SQL> alter table dept drop column M_ROW$$;<br /><br /> - Issue the drop materialized view command for each materialized view to<br /> clean up the data dictionary references to them:<br /> <br /> SQL> drop materialized view dept;<br /><br /> - Issue the create materialized view command using the new Oracle8i <br /> clause 'on prebuilt table'. It is not possible to create the <br /> materialized view as rowid, an error ORA-12058 will be raised in case<br /> you try to create the materialized view as rowid:<br /> <br /> SQL> create materialized view dept<br /> on prebuilt table<br /> refresh force with primary key<br /> as select * from scott.dept@<master_global_db_name><br /> <br /> <br />References<br />==========<br /></font><a href="http://ningoo.itpub.net/metalink/plsql/showdoc?db=NOT&id=258227.1&blackframe=1"><font face="Courier" size="2">Note 258227.1</font></a><font face="Courier" size="2"> Overview of the types of MVIEW available in Oracle 8 and 9i</font><a href="http://ningoo.itpub.net/metalink/plsql/showdoc?db=NOT&id=179466.1&blackframe=1"><font face="Courier" size="2">Note 179466.1</font></a><font face="Courier" size="2"> Diagnosing ORA-12015 fast refresh materialized view / <br /> complex queries</font><a href="http://ningoo.itpub.net/metalink/plsql/showdoc?db=NOT&id=179466.1&blackframe=1"><font face="Courier" size="2">Note 179466.1</font></a><font face="Courier" size="2"> Diagnosing ORA-12034 Materialized View Log Younger than <br /> Last Refresh</font><a href="http://ningoo.itpub.net/metalink/plsql/showdoc?db=NOT&id=179469.1&blackframe=1"><font face="Courier" size="2">Note 179469.1</font></a><font face="Courier" size="2"> Diagnosing ORA-12004 Refresh Fast Cannot be Used<br /><br />Oracle8 Replication Release 2 <br />Oracle8i SQL Reference Release 3 <br />Oracle8i Replication Release 2 <br />Oracle8i Data Warehousing Guide Release 2 <br />Oracle9i SQL Reference Release 2 <br />Oracle9i Advanced Replication Release 2 (9.2)<br />Oracle9i Data Warehousing Guide Release 2</font> <p style="clear:both;"></p> <p class="translate"> 来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/193161/viewspace-50263/,如需转载,请注明出处,否则将追究法律责任。 </p> </div> </div> <div class="preview-footer clearfix"> <!--已点赞、收藏--> <div class="icon-praise" id="praise" onclick="blogObj.praise($(this),'/praise/')"><span class="text"></span><span class="num">0</span></div> <div class="icon-collect" id="collect" onclick="blogObj.praise($(this),'/favour/')"><span class="text"></span><span class="num">0</span></div> <div class="share-group fr"> <div class="bdsharebuttonbox bdshare-button-style0-16" data-bd-bind="1522216707615"> <span class="fl">分享到:</span> <a href="javascript:;" class="bds_tsina" data-cmd="tsina" title="分享到新浪微博"></a> <a href="javascript:;" class="bds_sqq" data-cmd="sqq" title="分享到QQ好友"></a> <a href="javascript:;" class="bds_qzone" data-cmd="qzone" title="分享到QQ空间"></a> <a href="javascript:;" class="bds_weixin" data-cmd="weixin" title="分享到微信"></a> </div> <script> window._bd_share_config = { "common": { "bdSnsKey": {}, "bdText": "", "bdDesc": "", "bdMini": "", "bdMiniList": false, "bdPic": "", "bdStyle": "0", "bdSize": "32" }, "share": {"bdSize": 16} }; with (document) 0[(getElementsByTagName('head')[0] || body).appendChild(createElement('script')).src = 'http://bdimg.share.baidu.com/static/api/js/share.js?v=89860593.js?cdnversion=' + ~(-new Date() / 36e5)]; if ($(window).width() < 1320) { if ($(window).width() < 1200) { $(".sideslip").css({ "left": "20px", "margin-left": 0 }) } else { $(".sideslip").css({ "margin-left": "-590px" }) } }</script> </div> </div> <div class="article-context"> <div class="fl"> <span class="color77">上一篇:</span> <a href="http://blog.itpub.net/193161/viewspace-50262/">Materialized View Refresh : Log Population and Purge</a> </div> <div class="fr"> <span class="color77">下一篇:</span> <a href="http://blog.itpub.net/193161/viewspace-50264/">ORA-12051 or ORA-12054 when Creating Local Materialized View</a> </div> </div> <input type="hidden" id="blogId" value="50263"> <!--评论开始--> <div class="blog-comment"> <div class="new-comment"> <input type="hidden" id="hid" name="hid" value="50263"> <input type="hidden" name="_token" value="PaCX7GV1rVqOAFWaawnNEr7uDolvt20doCu95SRW"> <a class="avatar" href="javascript:;"> <img src="http://blog.itpub.net/images/user_pic_default.png"></a> <!--用户未登录--> <div class="sign-container"> <span>请登录后发表评论</span> <a class="btn btn-sign"> <a class="layui-btn ml30" onclick="blog.login(event)">登录</a> </a> </div> <!--用户已登录--> <textarea placeholder="请写下你的评论…" onfocus="blogObj.textareaFocus($(this),200)" maxlength=200></textarea> </div> <div class="comment-list"> <div class="top-title"><span class="all-comment">全部评论</span> <span class="colorbb ml20"></span> </div> <div class="comment-items" id="comment_items"> <script type="text/template" id='blog_template'> <%for(var i=0;i <items.length;i++){%> <div class="comment-item"> <div class="author"> <a target="_blank" href="/<%=items[i].uid%>" class="avatar"> <img src="<%=items[i].headimg%>" alt=""> </a> <div class="info"> <div class="name"><a target="_blank" href="/<%=items[i].uid%>"><%=items[i].username%></a> <span class="fr reply-btn" onclick="blogObj.newComment($(this),'<%=items[i].username%>',<%=items[i].id%>)">回复</span></div> <div class="time colorbb"><%=items[i].createtime%></div> </div> </div> <p class="comment-wrap"><%=items[i].content%></p> <%if(items[i].items.items.length) { %> <div class="sub-comment-list"> <%for(var j=0;j<items[i].items.items.length;j++){%> <div class="sub-comment"> <div class="color77 time-reply"> <span class="time"><%=items[i].items.items[j].createtime%></span> <span class="reply-btn fr" onclick="blogObj.newComment($(this),'<%=items[i].items.items[j].username%>',<%=items[i].items.items[j].id%>)">回复</span> </div> <p><a href="/<%=items[i].uid%>"><%=items[i].items.items[j].username%></a>   回复   <a href="/<%=items[i].touid%>"><%=items[i].items.items[j].tousername%></a>: <span class="content"><%=items[i].items.items[j].content%></span></p> </div> <%}%> <%if(items[i].items.total > 5) { %> <div class="more-comment"><span class="sub-comment-count">还有<i class="count"><%=items[i].items.total-5%></i>条评论</span><span class="more-comment-btn" onclick=blogObj.loadSubComment($(this),<%=items[i].id%>) data-count=1 data-flag=true>点击查看</span></div> <%}%> </div> <%}%> </div> <%}%> </script> </div> </div> </div> <!--评论结束--> </div> <script> </script> <div class="fr w290"> <!--作者信息开始--> <!--作者信息开始--> <div class="author-info right-fixed "> <div class="head-img"> <a href="http://blog.itpub.net/193161/"><img src="http://account.itpub.net/api/avatar.php?uid=193161" alt=""></a> </div> <div class="author-name"><a href="http://blog.itpub.net/193161/">NinGoo</a></div> <div class="author-intro"> </div> <p class="register-time"><span class="color77">注册时间:</span>2004-12-07</p> <ul class="tree-list clearfix"> <li> <div class="item-tt">博文量</div> <a href="http://blog.itpub.net/193161/"><span class="item-num blognum">200</span></a> </li> <li> <div class="item-tt">访问量</div> <div class="item-num blogviewnum">133196</div> </li> </ul> </div> <!--作者信息结束--> <script> var data = {uid: 193161}; $._ajax({ url: '/getAuthorInfo/', data: data, type: 'get', dataType: 'json', success: function (data) { if (data.code == 200) { var data = data.data; $(".blognum").text(data.blognum); $(".blogviewnum").text(data.visitednum); } } }); </script> <!--作者信息结束--> <!--博文推荐开始--> <div class="blog-choice right-fixed"> <h3 class="choice-title">最新文章</h3> <ul class="newul"> <li class="new-item"> <a href="http://blog.itpub.net/193161/viewspace-50185/" class="clearfix"> Data Warehouse Guide文档笔记(三):RELY constraints </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/193161/viewspace-50285/" class="clearfix"> 升级到10.2.0.3 </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/193161/viewspace-50281/" class="clearfix"> 同一会话中的自身死锁情况 </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/193161/viewspace-50335/" class="clearfix"> SQL Server 2005之Dedicated Administration Connection </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/193161/viewspace-50300/" class="clearfix"> Internal Implementation of Oracle Locks (Enqueue) </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/193161/viewspace-50252/" class="clearfix"> EVENT: DROP_SEGMENTS - Forcing cleanup of TEMPORARY segments </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/193161/viewspace-50315/" class="clearfix"> Redhat AS4 Linux常用服务和工具 </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/193161/viewspace-50313/" class="clearfix"> How to Relink Oracle Database Software on UNIX </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/193161/viewspace-50320/" class="clearfix"> oracle的index最多可以包含多少个列? </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/193161/viewspace-50255/" class="clearfix"> FAQ - Flash Recovery Area feature of 10G </a> </li> <ul> </div> <!--大牛精选结束--> </div> </div> <div class="icon-back-top" onclick="$('html,body').animate({scrollTop:0},'slow');"></div> <!--main部分结束--> <!--百度推送--> <script> (function(){ var bp = document.createElement('script'); var curProtocol = window.location.protocol.split(':')[0]; if (curProtocol === 'https') { bp.src = 'https://zz.bdstatic.com/linksubmit/push.js'; } else { bp.src = 'http://push.zhanzhang.baidu.com/push.js'; } var s = document.getElementsByTagName("script")[0]; s.parentNode.insertBefore(bp, s); })(); </script> <!--2019-11-18 12:25:09--> <!--footer部分开始--> <div class="blog-footer"> <div class="w1200 pr"> <div class="footer-links"> <a class="icon-blog icon-wx mr30"> <div class="wx-qrcode"> <img src="http://edu.itpub.net/images/qrcode.jpg" alt=""> </div> </a> <a href="https://weibo.com/itpub2001?from=myfollow_all" class="icon-blog icon-wb" target="_blank"></a> </div> <p class="footer-nav"> <a href="http://www.it168.com/bottomfile/it168.shtml" target="_blank">支持我们</a> <a href="http://www.it168.com/bottomfile/tgzn.shtml" target="_blank">作者招募</a> <a href="http://www.it168.com/bottomfile/sytk.shtml" target="_blank">用户协议</a> <a href="http://blog.itpub.net/31509949/viewspace-2157750/" target="_blank">FAQ</a> <a href="http://edu.itpub.net/contactus.html" target="_blank">Contact Us</a> <script src="https://s22.cnzz.com/z_stat.php?id=1274521965&web_id=1274521965" language="JavaScript"></script> </p> <p>北京盛拓优讯信息技术有限公司. 版权所有  <a style="color:#777777;" target="_blank" href="http://beian.miit.gov.cn">京ICP备09055130号-4</a>  北京市公安局海淀分局网监中心备案编号:11010802021510</p> <p>广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员</p> </div> </div> <!--footer部分结束--> <script> var _hmt = _hmt || []; (function() { var hm = document.createElement("script"); hm.src = "https://hm.baidu.com/hm.js?5016281862f595e78ffa42f085ea0f49"; var s = document.getElementsByTagName("script")[0]; s.parentNode.insertBefore(hm, s); })(); </script> <!-- END STAT PV --> </body> </html>