Tuesday, August 27, 2013

Data Pump 12c – Pumping Data with the LOGTIME Parameter

Since its release, Oracle Data Pump has been a worthy successor to the traditional exp/imp tools.  However, one area lacking with Data Pump has been something as simple as the ability to identify how long each step of a Data Pump job actually takes.  The log will show start time at the top of the log and end time at the bottom, but the time of execution for each step is a mystery.  Oracle 12c solves this problem with the LOGTIME parameter, which adds a timestamp to the execution of each step of the Data Pump job.  Here’s what it looks like without the parameter.

/home/oracle:test1:expdp altdotoracle/altdotoracle \
> directory=data_pump_dir dumpfile=expdp.dmp \
> tables=employee

Export: Release 12.1.0.1.0 - Production on Tue Aug 13 09:32:38 2013

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "ALTDOTORACLE"."SYS_EXPORT_TABLE_01":  altdotoracle/******** directory=data_pump_dir dumpfile=expdp.dmp tables=employee
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "ALTDOTORACLE"."EMPLOYEE"                   10.93 KB      16 rows
Master table "ALTDOTORACLE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ALTDOTORACLE.SYS_EXPORT_TABLE_01 is:
  /oracle/base/admin/test1/dpdump/expdp.dmp
Job "ALTDOTORACLE"."SYS_EXPORT_TABLE_01" successfully completed at Tue Aug 13 09:32:51 2013 elapsed 0 00:00:11

With the LOGTIME parameter, each step is prefixed with a timestamp, indicating the start time for each event that is processed.

/home/oracle:test1:expdp altdotoracle/altdotoracle \
> directory=data_pump_dir dumpfile=expdp.dmp \
> tables=employee LOGTIME=ALL

Export: Release 12.1.0.1.0 - Production on Tue Aug 13 09:34:54 2013

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
13-AUG-13 09:34:56.757: Starting "ALTDOTORACLE"."SYS_EXPORT_TABLE_01":  altdotoracle/******** directory=data_pump_dir dumpfile=expdp.dmp tables=employee LOGTIME=ALL
13-AUG-13 09:34:57.019: Estimate in progress using BLOCKS method...
13-AUG-13 09:34:57.364: Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
13-AUG-13 09:34:57.396: Total estimation using BLOCKS method: 64 KB
13-AUG-13 09:34:57.742: Processing object type TABLE_EXPORT/TABLE/TABLE
13-AUG-13 09:34:57.894: Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
13-AUG-13 09:34:57.964: Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
13-AUG-13 09:35:04.853: . . exported "ALTDOTORACLE"."EMPLOYEE"   10.93 KB      16 rows
13-AUG-13 09:35:05.123: Master table "ALTDOTORACLE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
13-AUG-13 09:35:05.127: ******************************************************************************
13-AUG-13 09:35:05.128: Dump file set for ALTDOTORACLE.SYS_EXPORT_TABLE_01 is:
13-AUG-13 09:35:05.131:   /oracle/base/admin/test1/dpdump/expdp.dmp
13-AUG-13 09:35:05.134: Job "ALTDOTORACLE"."SYS_EXPORT_TABLE_01" successfully completed at Tue Aug 13 09:35:05 2013 elapsed 0 00:00:09

The parameter works similarly with Data Pump Import.  Note that, although it is documented, the LOGTIME parameter is not described when you do a expdp help=y or impdp help=y command.

1 comment:

  1. Good info..
    I found lot of useful datapump reference examples here.
    http://www.acehints.com/p/datapump.html

    ReplyDelete