materialized view exp imp(物化视图的导出导入)

首页 » ORACLE 9i-23c » materialized view exp imp(物化视图的导出导入)

materialized view exp imp(物化视图的导出导入)

2011/09/07
ORACLE 9i-23c
3,977 views
2 条评论

物化视图的快速刷新要求基本必须建立物化视图日志，物化视图日志的名称为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

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

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时会报错，提示还建立在导出的用户下，所以名称已存在，但那个日志的表确实已导入，只不过它不再是视图日志

打赏

oracle物化视图

目前这篇文章有2条评论(Rss)评论关闭。

Bennett Lilienthal | #1
2011-12-21 at 12:57

Hello. magnificent job. I did not anticipate this. This is a great story. Thanks!
diablo 3 | #2
2011-11-27 at 00:19

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.

上一篇： limit backup filesize with rman maxpiecesize (限制备份文件大小相关参数)

下一篇： have recyclebin objects tablespace can be drop?

ANBOB™

materialized view exp imp(物化视图的导出导入)

目前这篇文章有2条评论(Rss)评论关闭。

最新文章

标签云集

文章索引

MySql Link

ORACLE Link

Others Link

国内好友

管理功能

微信公众号