物化视图是否可以exp导出_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