In Oracle 12C new parameter for Oracle Data Pump can be specified LOGTIME. It defines if Data Pump messages are timestamped so you can quickly figure out elapsed time between different phases of a Data Pump. Very useful to analyze performance problems and general timings for Data Pump processes.
Syntax
LOGTIME=[NONE | STATUS | LOGFILE | ALL]
Description:
NONE – No timestamps on status or log file messages (same as default)
STATUS – Timestamps on status messages only
- LOGFILE – Timestamps on log file messages only
ALL – Timestamps on both status and log file messages
Example
In following examples I just export single table
create table test_tbl ( id1 number, id2 number ); begin for i in 1..10 loop insert into test_tbl values(i, i); end loop; commit; end; / create directory temp_dir as '\temp';
NONE
It looks like in previous releases no extra information in log or output messages
expdp logtime=none directory=temp_dir \ tables=test_tbl reuse_dumpfiles=y Export: Release 12.1.0.1.0 - Production on Thu Oct 17 12:31:00 2013 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. Username: tomasz@pora12c1 Password: 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 "TOMASZ"."SYS_EXPORT_TABLE_01": tomasz/********@pora12c1 logtime=none directory=temp_dir tables=test_tbl reuse _dumpfiles=y Estimate in progress using BLOCKS method... Total estimation using BLOCKS method: 0 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 Master table "TOMASZ"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for TOMASZ.SYS_EXPORT_TABLE_01 is: \temp\expdat.dmp Job "TOMASZ"."SYS_EXPORT_TABLE_01" successfully completed at Thu Oct 17 12:32:01 2013 elapsed 0 00:00:52
ALL
Now you can see timestamps in each output message
expdp logtime=all directory=temp_dir \ tables=test_tbl reuse_dumpfiles=y Export: Release 12.1.0.1.0 - Production on Thu Oct 17 12:28:19 2013 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. Username: tomasz@pora12c1 Password: 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 17-OCT-13 12:28:30.150: Starting "TOMASZ"."SYS_EXPORT_TABLE_01": tomasz/********@pora12c1 logtime=all directory=temp_d r tables=test_tbl reuse_dumpfiles=y 17-OCT-13 12:28:31.085: Estimate in progress using BLOCKS method... 17-OCT-13 12:28:32.828: Total estimation using BLOCKS method: 0 KB 17-OCT-13 12:28:57.321: Processing object type TABLE_EXPORT/TABLE/TABLE 17-OCT-13 12:28:58.630: Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 17-OCT-13 12:28:59.081: Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER 17-OCT-13 12:29:19.495: Master table "TOMASZ"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded 17-OCT-13 12:29:19.498: ****************************************************************************** 17-OCT-13 12:29:19.499: Dump file set for TOMASZ.SYS_EXPORT_TABLE_01 is: 17-OCT-13 12:29:19.504: \temp\expdat.dmp 17-OCT-13 12:29:19.593: Job "TOMASZ"."SYS_EXPORT_TABLE_01" successfully completed at Thu Oct 17 12:29:19 2013 elapsed 0 00:00:52
The parameter can be defined as well in PL/SQL package DBMS_DATAPUMP using procedure SET_PARAMETER.
Have a fun 🙂
Tomasz