KEEP_MASTER and METRICS in EXPDP/IMDP

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s