关键字: 物化视图, migration,materialized views,快照,prebuilt table

对于要求停机时间很短的迁移,可以一试.

a) create empty tables in new database....
b) put constraints etc on them (deferrable please, important for mview groups!)
c) refresh them (fill them up)
d) put mv logs on remote tables
e) keep fast refreshing after that (could switch d/c above to avoid possible double complete refresh)
f) when ready to cut over -- kill access to old db, do a last refresh -- drop materialized views and there you go.

> create table emp as select * from where 1=0;
Table created.

> create table dept as select * from where 1=0;
Table created.

> alter table emp add constraint emp_pk primary key ( empno ) deferrable;
Table altered.

> alter table dept add constraint dept_pk primary key ( deptno ) deferrable;
Table altered.

> alter table emp add constraint emp_fk_dept foreign key (deptno ) references dept(deptno) deferrable;
Table altered.

> create materialized view emp on prebuilt table refresh complete as
select * from ;
Materialized view created.

> create materialized view dept on prebuilt table
refresh complete as select * from ;
Materialized view created.

> begin
dbms_refresh.make(
name =>'group1',
list =>'emp,dept',
next_date =>sysdate,
interval =>'sysdate+1/24',
implicit_destroy =>true);
end;
/

PL/SQL procedure successfully completed.

> exec dbms_refresh.refresh('GROUP1' );
PL/SQL procedure successfully completed.

> select mview_name, last_refresh_type from user_mviews;

MVIEW_NAME LAST_REF
------------------------------ --------
DEPT COMPLETE
EMP COMPLETE

> @connect
> set termout off
> REM GET afiedt.buf NOLIST
> set termout on
> create materialized view log on emp;
Materialized view log created.

> create materialized view log on dept;
Materialized view log created.

> @connect /
> set termout off
> REM GET afiedt.buf NOLIST
> set termout on
> alter materialized view emp refresh fast;
Materialized view altered.

> alter materialized view dept refresh fast;
Materialized view altered.

> @connect
> set termout off
> REM GET afiedt.buf NOLIST
> set termout on
> update emp set ename = initcap(ename) where rownum = 1;
1 row updated.

> update dept set dname = initcap(dname) where rownum = 1;
1 row updated.

> commit;
Commit complete.

>
> @connect /
> set termout off
> REM GET afiedt.buf NOLIST
> set termout on
> exec dbms_refresh.refresh( 'GROUP1' );

PL/SQL procedure successfully completed.

> select mview_name, last_refresh_type from user_mviews;

MVIEW_NAME LAST_REF
------------------------------ --------
DEPT FAST
EMP FAST

> drop materialized view emp;
Materialized view dropped.

> drop materialized view dept;
Materialized view dropped.

> select count(*) from emp;

COUNT(*)
----------
14

> select count(*) from dept;

COUNT(*)
----------
4