物化视图的快速刷新要求基本必须建立物化视图日志，物化视图日志的名称为MLOG$_加表名称，如果对象已经存在为在日志名称后加数字序列如mlog$_test1，下面就测试一把物化视图的导入导出
SQL> conn anbob/anbob
Connected.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> create table testmv(id int);
Table created.
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
ANBOB CONNECT NO YES NO
ANBOB PLUSTRACE NO YES NO
ANBOB RESOURCE NO YES NO
SQL> create materialized view log on testmv;
create materialized view log on testmv
*
ERROR at line 1:
ORA-12014: table 'TESTMV' does not contain a primary key constraint
SQL> drop table testmv purge;
Table dropped.
SQL> create table testmv(id int primary key, name varchar2(10));
Table created.
SQL> create materialized view log on testmv;
Materialized view log created.
SQL> create materialized view mv_testmv as
2 select * from testmv;
select * from testmv
*
ERROR at line 2:
ORA-01031: insufficient privileges
SQL> conn system/oracle
Connected.
SQL> grant create materialized view to anbob;
Grant succeeded.
SQL> conn anbob/anbob
Connected.
SQL> create materialized view mv_testmv as
2 select * from testmv;
Materialized view created.
SQL> insert into testmv values(1,'andy bobo');
1 row created.
SQL> commit;
Commit complete.
SQL> col change_vector$$ for a40
SQL> select * from mlog$_testmv;
ID SNAPTIME$$ D O CHANGE_VECTOR$$
---------- ------------------- - - ----------------------------------------
1 4000-01-01 00:00:00 I N FE
SQL> select * from mv_testmv;
no rows selected
SQL> exec dbms_mview.refresh('mv_testmv');
PL/SQL procedure successfully completed.
SQL> select * from mv_testmv;
ID NAME
---------- ----------
1 andy bobo
SQL> host
[oracle@aix ~]$ exp anbob/anbob file=anbob log=exp.log
Export: Release 10.2.0.4.0 - Production on Wed Sep 7 16:41:20 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ANBOB
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ANBOB
About to export ANBOB's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ANBOB's tables via Conventional Path ...
. . exporting table MLOG$_TESTMV 0 rows exported
. . exporting table MV_TESTMV 1 rows exported
. . exporting table RUPD$_TESTMV
. . exporting table STUSC 3 rows exported
. . exporting table TEST 1 rows exported
. . exporting table TESTA 10000 rows exported
. . exporting table TESTB 3002 rows exported
. . exporting table TESTBLOB 4 rows exported
. . exporting table TESTC 1001 rows exported
. . exporting table TESTIMG 6 rows exported
. . exporting table TESTKDR 2 rows exported
. . exporting table TESTMV 1 rows exported
. . exporting table TESTSPLI
. . exporting partition P_2007 1 rows exported
. . exporting partition P_2008 1 rows exported
. . exporting partition P_2009 1 rows exported
. . exporting partition P_MORE 4 rows exported
. . exporting table TESTXY 13 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
[oracle@aix ~]$ exit
exit
SQL> conn system/oracle
Connected.
SQL> create user weejar identified by weejar;
User created.
SQL> grant connect,resource to weejar;
Grant succeeded.
SQL> grant create materialized view to weejar;
Grant succeeded.
SQL> host
[oracle@aix ~]$ ls
anbob.dmp exp.log tt
[oracle@aix ~]$ imp weejar/weejar file=anbob.dmp full=y
Import: Release 10.2.0.4.0 - Production on Wed Sep 7 16:44:20 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ANBOB, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing ANBOB's objects into WEEJAR
. . importing table "MLOG$_TESTMV" 0 rows imported
. . importing table "MV_TESTMV" 1 rows imported
. . importing table "STUSC" 3 rows imported
. . importing table "TEST" 1 rows imported
. . importing table "TESTA" 10000 rows imported
. . importing table "TESTB" 3002 rows imported
. . importing table "TESTBLOB" 4 rows imported
. . importing table "TESTC" 1001 rows imported
. . importing table "TESTIMG" 6 rows imported
. . importing table "TESTKDR" 2 rows imported
. . importing table "TESTMV" 1 rows imported
. . importing partition "TESTSPLI":"P_2007" 1 rows imported
. . importing partition "TESTSPLI":"P_2008" 1 rows imported
. . importing partition "TESTSPLI":"P_2009" 1 rows imported
. . importing partition "TESTSPLI":"P_MORE" 4 rows imported
. . importing table "TESTXY" 13 rows imported
IMP-00017: following statement failed with ORACLE error 1031:
"BEGIN SYS.DBMS_SNAPSHOT_UTL.SYNC_UP_LOG('ANBOB','TESTMV'); END;"
IMP-00003: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_ISNAPSHOT", line 108
ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 1666
ORA-06512: at line 1
IMP-00015: following statement failed because the object already exists:
"CREATE SNAPSHOT LOG ON "ANBOB"."TESTMV" WITH PRIMARY KEY EXCLUDING NEW VALU"
"ES USING ("MLOG$_TESTMV", (7, 'ORCL', 98, '2011-09-07:16:40:34', '2011-09-0"
"7:16:40:34', '2011-09-07:16:33:19', '4000-01-01:00:00:00','4000-01-01:00:00"
":00','4000-01-01:00:00:00', 1, "ID", '2011-09-07:16:33:19', 2, 1, 285, '201"
"1-09-07:16:40:34', ("RUPD$_TESTMV")))"
Import terminated successfully with warnings.
SQL> conn weejar/weejar
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
MLOG$_TESTMV TABLE
MV_TESTMV TABLE
RUPD$_TESTMV TABLE
STUSC TABLE
TEST TABLE
TESTA TABLE
TESTB TABLE
TESTBLOB TABLE
TESTC TABLE
TESTIMG TABLE
TESTKDR TABLE
TESTMV TABLE
TESTSPLI TABLE
TESTXY TABLE
14 rows selected.
SQL> select owner,mview_name from user_mviews;
OWNER MVIEW_NAME
------------------------------ ------------------------------
WEEJAR MV_TESTMV
SQL> select * from user_mview_logs;
no rows selected
SQL> desc MLOG$_TESTMV
Name Null? Type
--------------- -------- --------------------------------
ID NUMBER
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
SQL> select * from MLOG$_TESTMV;
no rows selected
SQL> insert into testmv values(2,'weejar.com');
1 row created.
SQL> commit;
Commit complete.
SQL> exec dbms_mview.refresh('mv_testmv');
BEGIN dbms_mview.refresh('mv_testmv'); END;
*
ERROR at line 1:
ORA-12018: following error encountered during code generation for "WEEJAR"."MV_TESTMV"
ORA-01741: illegal zero-length identifier
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2251
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2457
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2426
ORA-06512: at line 1
SQL> create materialized view log on testmv;
Materialized view log created.
SQL> select * from testmv;
ID NAME
---------- ----------
1 andy bobo
2 weejar.com
SQL> select * from user_mview_logs;
LOG_OWNER MASTER LOG_TABLE
------------------------------ ------------------------------ ------------------------------
WEEJAR TESTMV MLOG$_TESTMV1
SQL> select * from MLOG$_TESTMV1;
no rows selected
SQL> exec dbms_mview.refresh('mv_testmv');
PL/SQL procedure successfully completed.
SQL> select * from mv_testmv;
ID NAME
---------- ----------
1 andy bobo
SQL> insert into testmv values(3,'weejar.com');
1 row created.
SQL> select * from mv_testmv;
ID NAME
---------- ----------
1 andy bobo
SQL> select * from MLOG$_TESTMV1;
ID SNAPTIME$$ D O CHANGE_VECTOR$$
---------- ------------------- - - ----------------------------------------
3 4000-01-01 00:00:00 I N FE
SQL> exec dbms_mview.refresh('mv_testmv');
PL/SQL procedure successfully completed.
SQL> select * from mv_testmv;
ID NAME
---------- ----------
1 andy bobo
3 weejar.com
note:
物化视图日志在imp时会报错，提示还建立在导出的用户下，所以名称已存在，但那个日志的表确实已导入，只不过它不再是视图日志
Hello. magnificent job. I did not anticipate this. This is a great story. Thanks!
Hello, just required you to know I he added your internet site to my Google bookmarks due to your layout. But seriously, I feel your web website has 1 in the freshest theme I??ve came across. It extremely helps make looking at your website significantly easier.