mview on prebuilt table用法尝试

物化视图的on prebuilt table特性可以用于减少跨平台不同host之间数据迁移的业务中断时间,对于7*24小时的应用系统是很实用的,以下是在同一台host上的试验,主要是掌握原理.对于不同host的实际情况,无非是多建立一个dblink的问题.

[@more@]

SQL> create table a (col1 varchar2(20),col2 number);

Table created.

SQL> create table b (col1 varchar2(20),col2 number);

Table created.

SQL> alter table a add constraint pk_a primary key(col1);

Table altered.

SQL> insert into a values('1',1);

1 row created.

SQL> insert into a values('2',2);

1 row created.

SQL> commit;

Commit complete.

SQL> create materialized view log on a with primary key;

Materialized view log created.

SQL> create materialized view b on prebuilt table
2 as select * from a;

Materialized view created.

SQL> select object_name,object_type from user_objects
2 where object_name='B';

OBJECT_NAME

OBJECT_TYPE

----------------------------------------------
B TABLE

B

MATERIALIZED VIEW

2 rows selected.

SQL> select count(*) from b;

COUNT(*)

----------

0

1 row selected.

SQL> exec dbms_mview.refresh('b','c');

PL/SQL procedure successfully completed.

SQL> select * from b;

COL1 COL2

-------------------- ----------

1 1

2 2

2 rows selected.

SQL> insert into a values('3','3');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from a;

COL1 COL2

-------------------- ----------

1 1

2 2

3 3

3 rows selected.

SQL> insert into a values('4',4);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_mview.refresh('b','f');

PL/SQL procedure successfully completed.

SQL> select * from b;

COL1 COL2

-------------------- ----------

1 1

2 2

3 3

4 4

4 rows selected.

SQL> select object_name,object_type from user_objects
2 where object_name='B';

OBJECT_NAME OBJECT_TYPE

------------------------------------------------------------
B TABLE

B MATERIALIZED VIEW

2 rows selected.

SQL> drop materialized view b;

Materialized view dropped.

SQL> select object_name,object_type from user_objects
2 where object_name='B';

OBJECT_NAME OBJECT_TYPE

------------------------------------------------------
B TABLE

1 row selected.

SQL> select * from b;

COL1 COL2

-------------------- ----------

1 1

2 2

3 3

4 4

4 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/75730/viewspace-811685/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/75730/viewspace-811685/