Oracle11gR2导入导出实战之物化视图prebuilt

源实例上创建表 物化视图

oracle@localhost admin]$ sqlplus system/oracle@orcl2

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 3 17:33:01 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL>
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl2

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost admin]$ sqlplus test_tts/oracle@orcl2

SQL> select * from cat;

TABLE_NAME TABLE_TYPE
------------------------------ -----------
TEST_TTS TABLE
TEST_TTS2 TABLE
TEST_TTS3 TABLE

SQL> create table test_mv as select object_id,object_name from all_objects;

Table created.

SQL> alter table test_mv modify(object_id primary key);

Table altered.

SQL> insert into test_mv select object_id+100000,object_name from all_objects;

68313 rows created.

SQL> insert into test_mv select object_id+1000000,object_name from test_mv;

136625 rows created.

SQL> commit;

Commit complete.

SQL> create materialized view log on test_mv ;

Materialized view log created.

目标实例上创建链接数据库

[oracle@localhost ~]$ export ORACLE_SID=orcl
[oracle@localhost ~]$ sqlplus / as sysdba

SQL> grant create database link to test_tts;

Grant succeeded.

SQL> conn test_tts/oracle
Connected.

SQL> create database link link_orcl2 connect to test_tts identified by oracle using 'orcl2';

Database link created.

SQL> select * from cat@link_orcl2;

TABLE_NAME TABLE_TYPE
------------------------------ -----------
MLOG$_TEST_MV TABLE
TEST_MV TABLE
TEST_TTS TABLE
TEST_TTS2 TABLE
TEST_TTS3 TABLE

创建表结构

SQL> create table test_mv as select * from test_mv@link_orcl2 where 1=2;

Table created.

创建可刷新的物化视图

SQL> create materialized view test_mv on prebuilt table refresh fast as select * from
2 test_mv@link_orcl2;

Materialized view created.

刷新数据

SQL> exec dbms_mview.refresh('TEST_MV','C');

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(1) FROM test_mv;

COUNT(1)
----------
273250

源端插入数据

SQL> INSERT INTO TEST_MV VALUES(1231200123,'aa');

1 row created.

SQL> commit;

Commit complete.

目标端快速刷新数据同步了

SQL> exec dbms_mview.refresh('TEST_MV','F');

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(1) FROM test_mv;

COUNT(1)
----------
273251

切换 源端删除物化视图

SQL> drop materialized view test_mv