How to Exclude MLOG$ Materialized View Log Tables from the Data Pump IMPDP

Generally if you use exclude parameter with materialized view, materialized view log, it will not exclude the mlog$ tables from the import. You have to useTABLE also with the exclude parameter. Please see an example here.

Example
I am going to create a materialized view and a log for the same.

SQL> create materialized view log on dept;


Materialized view log created.

create materialized view mv_dept as
select deptno
from  dept; 

Materialized view created.

Going to take the export of the schema.

expdp job_name=schemaexp1 schemas=sthomas dumpfile=exp_schem.dmplogfile=exp_schema.log directory=exp_dir

Export: Release 11.2.0.3.0 - Production on Wed Sep 11 08:23:37 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: sthomas
Password:

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Starting "STHOMAS"."SCHEMAEXP1":  sthomas/******** job_name=schemaexp1 schemas=sthomas dumpfile=exp_schem.dmp logfile=exp_schema.log directory=exp_dir
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG
. . exported "STHOMAS"."DEPT"                            5.945 KB       4 rows
. . exported "STHOMAS"."MV_DEPT"                         5.085 KB       4 rows
. . exported "STHOMAS"."MLOG$_DEPT"                          0 KB       0 rows
Master table "STHOMAS"."SCHEMAEXP1" successfully loaded/unloaded
******************************************************************************
Dump file set for STHOMAS.SCHEMAEXP1 is:
  /u07/exp_dir/exp_schem.dmp
Job "STHOMAS"."SCHEMAEXP1" successfully completed at 08:25:42

Suppose if you use below command to import the dump it will import the MLOG$ tables to the schema again.

impdp job_name=schemaexp1 schemas=sthomas dumpfile=exp_schem.dmp logfile=imp_schema.log directory=exp_dir EXCLUDE=MATERIALIZED_VIEW,MATERIALIZED_VIEW_LOG

Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "STHOMAS"."DEPT"                            5.945 KB       4 rows
. . imported "STHOMAS"."MV_DEPT"                         5.085 KB       4 rows
. . imported "STHOMAS"."MLOG$_DEPT"                          0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
MLOG$_DEPT                     TABLE
MV_DEPT                        TABLE
RUPD$_DEPT                     TABLE

You should use the table object also in the exclude list to avoid the mlog$ tables.
$ impdp job_name=schemaexp1 schemas=sthomas dumpfile=exp_schem.dmp logfile=imp_schema.log directory=exp_dir EXCLUDE=table:\"like \'MLOG\$\%\'\",MATERIALIZED_VIEW_LOG,MATERIALIZED_VIEW

Import: Release 11.2.0.3.0 - Production on Wed Sep 11 09:13:13 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Master table "SYS"."SCHEMAEXP1" successfully loaded/unloaded
Starting "SYS"."SCHEMAEXP1":  /******** AS SYSDBA job_name=schemaexp1 schemas=sthomasdumpfile=exp_schem.dmp logfile=imp_schema.log directory=exp_dir EXCLUDE=table:"like'MLOG$%'",MATERIALIZED_VIEW_LOG,MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "STHOMAS"."DEPT"                            5.945 KB       4 rows
. . imported "STHOMAS"."MV_DEPT"                         5.085 KB       4 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Job "SYS"."SCHEMAEXP1" successfully completed at 09:13:18
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-14 All Rights Reserved | Site Map | Contact | Disclaimer | Google