首页 » ORACLE » materialized view exp imp(物化视图的导出导入)

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

物化视图的快速刷新要求基本必须建立物化视图日志,物化视图日志的名称为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时会报错,提示还建立在导出的用户下,所以名称已存在,但那个日志的表确实已导入,只不过它不再是视图日志

打赏

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

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

    Hello. magnificent job. I did not anticipate this. This is a great story. Thanks!

  2. 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.