Recently, i came to know about “KEEP_MASTER” and “METRICS” , the undocumented parameter of EXPDP/IMPDP. METRICS provides the time it took for processing the objects and KEEP_MASTER prevents the Data Pump Master table from getting deleted after an Export/Import job completion.
Lets check —
D:\scripts>expdp directory=DATA_PUMP_DIR dumpfile=abc.dmp logfile=abc.log tables=TM_CONS,FAKE_IND_TEST metrics=y
Export: Release 11.2.0.2.0 - Production on Wed Aug 17 19:13:08 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: anand
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ANAND"."SYS_EXPORT_TABLE_01": anand/******** directory=DATA_PUMP_DIR dumpfile=abc.dmp logfile=abc.log tables=TM_CONS,FAKE_IND_TEST metrics=y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 152 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Completed 2 TABLE objects in 1 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Completed 1 INDEX objects in 1 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Completed 1 INDEX_STATISTICS objects in 1 seconds
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Completed 2 TABLE_STATISTICS objects in 0 seconds
. . exported "ANAND"."FAKE_IND_TEST" 62.61 MB 1000000 rows
. . exported "ANAND"."TM_CONS" 27.65 MB 871080 rows
Master table "ANAND"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ANAND.SYS_EXPORT_TABLE_01 is:
D:\ORACLE\APP\ADMIN\MATRIX\DPDUMP\ABC.DMP
Job "ANAND"."SYS_EXPORT_TABLE_01" successfully completed at 19:13:28
As, job completed successfully,the Export Master table “SYS_EXPORT_TABLE_01” will be dropped.
SQL> select owner,segment_name,segment_type,tablespace_name,(bytes/1024/1024)MB from dba_segments where segment_name='SYS_EXPORT_TABLE_01'; no rows selected SQL>
Now, lets see what happens when we use “KEEP_MASTER”.
D:\scripts>impdp directory=DATA_PUMP_DIR dumpfile=abc.dmp logfile=abc_imp_chk.log full=y metrics=y keep_master=y sqlfile=abc_sqlfile.lst
Import: Release 11.2.0.2.0 - Production on Wed Aug 17 19:15:05 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: anand
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ANAND"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "ANAND"."SYS_SQL_FILE_FULL_01": anand/******** directory=DATA_PUMP_DIR dumpfile=abc.dmp logfile=abc_imp_chk.log full=y metrics=y keep_master=y sqlfile=abc_sqlfile.lst
Processing object type TABLE_EXPORT/TABLE/TABLE
Completed 2 TABLE objects in 1 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Completed 1 INDEX objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Completed 1 INDEX_STATISTICS objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Completed 2 TABLE_STATISTICS objects in 1 seconds
Job "ANAND"."SYS_SQL_FILE_FULL_01" successfully completed at 19:15:14
SQL> select owner,segment_name,segment_type,tablespace_name,(bytes/1024/1024)MB from dba_segments where segment_name='SYS_SQL_FILE_FULL_01'; OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME MB --------------- ---------------------------------------- -------------------- ------------------------------ ---------- ANAND SYS_SQL_FILE_FULL_01 TABLE TEST .125 SQL> desc anand.SYS_SQL_FILE_FULL_01 Name Null? Type ----------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------------------- PROCESS_ORDER NUMBER DUPLICATE NUMBER DUMP_FILEID NUMBER DUMP_POSITION NUMBER DUMP_LENGTH NUMBER DUMP_ORIG_LENGTH NUMBER DUMP_ALLOCATION NUMBER COMPLETED_ROWS NUMBER ERROR_COUNT NUMBER ELAPSED_TIME NUMBER OBJECT_TYPE_PATH VARCHAR2(200) OBJECT_PATH_SEQNO NUMBER OBJECT_TYPE VARCHAR2(30) IN_PROGRESS CHAR(1) OBJECT_NAME VARCHAR2(500) OBJECT_LONG_NAME VARCHAR2(4000) OBJECT_SCHEMA VARCHAR2(30) ORIGINAL_OBJECT_SCHEMA VARCHAR2(30) ORIGINAL_OBJECT_NAME VARCHAR2(4000) PARTITION_NAME VARCHAR2(30) SUBPARTITION_NAME VARCHAR2(30) DATAOBJ_NUM NUMBER FLAGS NUMBER PROPERTY NUMBER TRIGFLAG NUMBER CREATION_LEVEL NUMBER COMPLETION_TIME DATE OBJECT_TABLESPACE VARCHAR2(30) SIZE_ESTIMATE NUMBER OBJECT_ROW NUMBER PROCESSING_STATE CHAR(1) PROCESSING_STATUS CHAR(1) BASE_PROCESS_ORDER NUMBER BASE_OBJECT_TYPE VARCHAR2(30) BASE_OBJECT_NAME VARCHAR2(30) BASE_OBJECT_SCHEMA VARCHAR2(30) ANCESTOR_PROCESS_ORDER NUMBER DOMAIN_PROCESS_ORDER NUMBER PARALLELIZATION NUMBER UNLOAD_METHOD NUMBER LOAD_METHOD NUMBER GRANULES NUMBER SCN NUMBER GRANTOR VARCHAR2(30) XML_CLOB CLOB PARENT_PROCESS_ORDER NUMBER NAME VARCHAR2(30) VALUE_T VARCHAR2(4000) VALUE_N NUMBER IS_DEFAULT NUMBER FILE_TYPE NUMBER USER_DIRECTORY VARCHAR2(4000) USER_FILE_NAME VARCHAR2(4000) FILE_NAME VARCHAR2(4000) EXTEND_SIZE NUMBER FILE_MAX_SIZE NUMBER PROCESS_NAME VARCHAR2(30) LAST_UPDATE DATE WORK_ITEM VARCHAR2(30) OBJECT_NUMBER NUMBER COMPLETED_BYTES NUMBER TOTAL_BYTES NUMBER METADATA_IO NUMBER DATA_IO NUMBER CUMULATIVE_TIME NUMBER PACKET_NUMBER NUMBER INSTANCE_ID NUMBER OLD_VALUE VARCHAR2(4000) SEED NUMBER LAST_FILE NUMBER USER_NAME VARCHAR2(30) OPERATION VARCHAR2(30) JOB_MODE VARCHAR2(30) QUEUE_TABNUM NUMBER CONTROL_QUEUE VARCHAR2(30) STATUS_QUEUE VARCHAR2(30) REMOTE_LINK VARCHAR2(4000) VERSION NUMBER JOB_VERSION VARCHAR2(30) DB_VERSION VARCHAR2(30) TIMEZONE VARCHAR2(64) STATE VARCHAR2(30) PHASE NUMBER GUID RAW(16) START_TIME DATE BLOCK_SIZE NUMBER METADATA_BUFFER_SIZE NUMBER DATA_BUFFER_SIZE NUMBER DEGREE NUMBER PLATFORM VARCHAR2(101) ABORT_STEP NUMBER INSTANCE VARCHAR2(60) CLUSTER_OK NUMBER SERVICE_NAME VARCHAR2(100) OBJECT_INT_OID VARCHAR2(32)
SQL> select OBJECT_TYPE,OBJECT_NAME,OBJECT_SCHEMA,ORIGINAL_OBJECT_SCHEMA,ORIGINAL_OBJECT_NAME,OBJECT_TABLESPACE,SIZE_ESTIMATE,OBJECT_ROW from SYS_SQL_FILE_FULL_01 where ORIGINAL_OBJECT_SCHEMA is not null; OBJECT_TYPE OBJECT_NAME OBJECT_SCHEMA ORIGINAL_OBJECT_SCHEMA ORIGINAL_OBJECT_NAME OBJECT_TABLESPACE SIZE_ESTIMATE OBJECT_ROW ------------------------------ --------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------- ---------- TABLE TM_CONS ANAND ANAND TM_CONS TEST 736 1 TABLE FAKE_IND_TEST ANAND ANAND FAKE_IND_TEST TEST 736 1 INDEX FAKE_CUST_ID ANAND ANAND FAKE_CUST_ID TEST 1 TABLE_DATA TM_CONS ANAND ANAND TM_CONS TEST 75497472 TABLE_DATA FAKE_IND_TEST ANAND ANAND FAKE_IND_TEST TEST 83886080
OBJECT_TYPE –> Show the object type.
OBJECT_SCHEMA –> Contains the schema name to which it has to be imported.
ORIGINAL_OBJECT_SCHEMA –> column has the original object’s schema name.
OBJECT_TABLESPACE –> Shows the tablespace where the object will be imported.
SIZE_ESTIMATE –> Estimated size of the table in bytes
This can be used to find owner,objects etc information contained in the dumpfile, in case you don’t know what dump contains. Definitely, we have sqlfile parameter to find the same, but this can also be one.
🙂
2 thoughts on “KEEP_MASTER and METRICS in EXPDP/IMDP”
Nice one
Thanks 🙂
Anand