LOGTIME Parameter for Oracle Data Pump Command Oracle Database 12C release 1 (12.1)

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

 

Leave a Reply

Your email address will not be published. Required fields are marked *