Master table reorganization with a primary key materialized view

An example is as follows:

1) This test is done with the scott.dept table

connect scott/tiger

2) If the dept table do not have primary key, add it using the following command:

alter table dept add constraint pk_dept primary key(deptno);

3) As a primary key exists on master table (dept), default materialized view log is
created with primary key. In case a primary key does not exist on the master table
an error ORA-12014 will be raised.

create materialized view log on dept;

4) Creation of materialized view ( default is primary key):
ORA-23415 will be raised in case the materialized view log do not record the
primary key

create materialized view s_dept refresh fast as select * from dept;

5) Perform insert in materialized view and commit it:

insert into dept (deptno) values (49);
commit;

If you select from the materialized view log you should see a row count of 1.

select count(*) from mlog$_dept;
1 row selected

6) Now we want to perform a reorganization of the master table
The below procedure will devalidate internal triggers which populates
the materialized view log when modification are done on the master table:

execute DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION('SCOTT','DEPT');

7) Now export the dept table:
exp scott/tiger tables=dept

8) Truncate the table to prepare it for import.

truncate table dept;

9) Import table dept.

imp scott/tiger tables=dept ignore=y

10) Turn on internals triggers.

execute DBMS_MVIEW.END_TABLE_REORGANIZATION('SCOTT','DEPT');

11) Refresh the materialized view, default is refresh method indicated in DBA_MVIEWS.

execute dbms_mview.refresh('S_DEPT','f');

12) If the materialized view is a rowid materialized view, then the error
ORA-12034 will appear at refresh time, and the materialized view will need
a complete refresh.

execute dbms_mview.refresh('S_DEPT','c');

Source: Metalink ID 254593.1