advertisements
_____________________________________________________________________________________________________________________
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
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment