DOYENSYS Knowledge Portal

We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal

Monday, January 19, 2015

Oracle Database 12c Feature for DataPump LOGTIME parameter

A useful new feature to timestamp every entry that datapump writes to the screen/log so you can easily see where the time is being spent in an export/import (though it may get a little confusing trying to work this out in a parallel process.

First create a few objects in out test schema after using TWO_TASK to set which container we want to go to (i still don't like having to type user/password@db for some reason i want it to be 'local' - sure I'll get used to that over time.........)

[oracle@oracle-server]:ED12G:[/oracle/diag/rdbms/orcl/ED12G/trace]# export TWO_TASK=//localhost:1525/
[oracle@oracle-server]:ED12G:[/oracle/diag/rdbms/orcl/ED12G/trace]# sqlplus test/test

SQL*Plus: Release Production on Tue Jul 23 13:30:04 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Tue Jul 23 2013 11:37:14 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create table a(col1 number);

Table created.

SQL> create view x as select * from a;

View created.

SQL> create synonym c for a;

Synonym created.

SQL> exit

Now export with the new logtime parameter

[oracle@server-name]:ED12G:[/oracle/diag/rdbms/orcl/ED12G/trace]# expdp test/test logtime=all directory=test reuse_dumpfiles=y

Export: Release - Production on Tue Jul 23 13:33:16 2013

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

Connected to: Oracle Database 12c Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
23-JUL-13 13:33:19.192: Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/******** logtime=all directory=test reuse_dumpfiles=y
23-JUL-13 13:33:19.347: Estimate in progress using BLOCKS method...
23-JUL-13 13:33:20.064: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
23-JUL-13 13:33:20.121: Total estimation using BLOCKS method: 0 KB
23-JUL-13 13:33:20.796: Processing object type SCHEMA_EXPORT/USER
23-JUL-13 13:33:20.832: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
23-JUL-13 13:33:20.839: Processing object type SCHEMA_EXPORT/ROLE_GRANT
23-JUL-13 13:33:20.846: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
23-JUL-13 13:33:21.174: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
23-JUL-13 13:33:21.185: Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
23-JUL-13 13:33:24.543: Processing object type SCHEMA_EXPORT/TABLE/TABLE
23-JUL-13 13:33:35.607: Processing object type SCHEMA_EXPORT/TABLE/COMMENT
23-JUL-13 13:33:36.203: Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
23-JUL-13 13:33:37.503: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
23-JUL-13 13:33:37.522: Processing object type SCHEMA_EXPORT/VIEW/VIEW
23-JUL-13 13:33:38.384: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
23-JUL-13 13:33:38.391: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
23-JUL-13 13:33:46.884: . . exported "TEST"."A"                                      0 KB       0 rows
23-JUL-13 13:33:47.421: Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
23-JUL-13 13:33:47.423: ******************************************************************************
23-JUL-13 13:33:47.424: Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
23-JUL-13 13:33:47.428:   /tmp/expdat.dmp
23-JUL-13 13:33:47.432: Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Jul 23 13:33:47 2013 elapsed 0 00:00:29

Here we can see highlighted in red the timestamp for each step and the total at the end. The oracle docs seem wrong at the moment there is no LOGTIME=LOGTIME setting and the values status and all seem to do the same thing. Nonetheless this is a nice new addition.

No comments: