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