How to export the dumpfile by using the LOGTIME parameter using expdp in Oracle database 19C
Posted by Mir Sayeed Hassan on November 24th, 2024
How to export the dumpfile by using the LOGTIME parameter using expdp in Oracle database 19C
Brief: – This parameter LOGTIME give us the timestamp for each and every export operation during expdp., therefore its easy to check the diagnise/elapsed time taken for execution.
– By default LOGTIME=NONE, If you want to use this feature use this parameter in expdp command.
LOGTIME parameters:
LOGTIME=NONE : Default value which will not display timestamp in both command prompt as well as export log file.
LOGTIME=ALL : Display the timestamp in both command prompt as well as export log file.
LOGTIME=LOGFILE : Display the timestamp in only export log file, not in command prompt.
LOGTIME=STATUS : Display the timestamp in only command prompt and not in export log file.
Use of LOGTIME=ALL: Display the timestamp in both command prompt as well as export log file.
Check the database version
SYS> select name, status, open_mode, version from V$database, v$instance; NAME STATUS OPEN_MODE VERSION ----------------------------------------------- ORA19CDB OPEN READ WRITE 19.0.0.0.0
[oracle@ora19cdb dump_bkp1]$ expdp directory=dump_bkp1 dumpfile=hassan_sch.dmp logfile=hassan_sch.log schemas=hassan LOGTIME=ALL Export: Release 19.0.0.0.0 - Production on Wed Nov 24 09:03:32 2024 Version 19.18.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 24-NOV-24 09:03:36.819: Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA directory=dump_bkp1 dumpfile=hassan_sch.dmp logfile=hassan_sch.log schemas=hassan LOGTIME=ALL 24-NOV-24 09:03:38.801: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA 24-NOV-24 09:03:39.041: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS 24-NOV-24 09:03:39.546: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 24-NOV-24 09:03:40.504: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER 24-NOV-24 09:03:41.058: Processing object type SCHEMA_EXPORT/USER 24-NOV-24 09:03:41.292: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT 24-NOV-24 09:03:41.401: Processing object type SCHEMA_EXPORT/ROLE_GRANT 24-NOV-24 09:03:41.497: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE 24-NOV-24 09:03:41.862: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA 24-NOV-24 09:03:44.756: Processing object type SCHEMA_EXPORT/TABLE/TABLE 24-NOV-24 09:03:48.166: Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE 24-NOV-24 09:03:48.888: Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE 24-NOV-24 09:03:52.963: Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX 24-NOV-24 09:03:59.818: . . exported "HASSAN"."BOWIE" 688.8 KB 10000 rows 24-NOV-24 09:03:59.821: . . exported "HASSAN"."ADD_PICTURE" 0 KB 0 rows 24-NOV-24 09:03:59.872: . . exported "HASSAN"."T1" 5.632 KB 9 rows 24-NOV-24 09:03:59.903: . . exported "HASSAN"."TEST1" 5.125 KB 4 rows 24-NOV-24 09:03:59.932: . . exported "HASSAN"."TEST2" 5.109 KB 2 rows 24-NOV-24 09:04:03.460: Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded 24-NOV-24 09:04:03.546: ****************************************************************************** 24-NOV-24 09:04:03.549: Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: 24-NOV-24 09:04:03.551: /u02/backup/dump_bkp1/hassan_sch.dmp 24-NOV-24 09:04:03.611: Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Nov 24 09:04:03 2024 elapsed 0 00:00:28
Use of LOGTIME=LOGFILE : Display the timestamp in only export log file, not in command prompt.
[oracle@ora19cdb dump_bkp1]$ expdp directory=dump_bkp1 dumpfile=hassan_sch1.dmp logfile=hassan_sch1.log schemas=hassan LOGTIME=LOGFILE Export: Release 19.0.0.0.0 - Production on Wed Nov 24 09:06:30 2024 Version 19.18.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA directory=dump_bkp1 dumpfile=hassan_sch1.dmp logfile=hassan_sch1.log schemas=hassan LOGTIME=LOGFILE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX . . exported "HASSAN"."BOWIE" 688.8 KB 10000 rows . . exported "HASSAN"."ADD_PICTURE" 0 KB 0 rows . . exported "HASSAN"."T1" 5.632 KB 9 rows . . exported "HASSAN"."TEST1" 5.125 KB 4 rows . . exported "HASSAN"."TEST2" 5.109 KB 2 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /u02/backup/dump_bkp1/hassan_sch1.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Nov 24 09:06:58 2024 elapsed 0 00:00:25
Verify the logfile:
[oracle@ora19cdb dump_bkp1]$ tail -5 hassan_sch1.log 24-NOV-24 09:06:57.996: Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded 24-NOV-24 09:06:58.078: ****************************************************************************** 24-NOV-24 09:06:58.079: Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: 24-NOV-24 09:06:58.081: /u02/backup/dump_bkp1/hassan_sch1.dmp 24-NOV-24 09:06:58.122: Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Nov 24 09:06:58 2024 elapsed 0 00:00:25
Use of LOGTIME=STATUS: Display the timestamp in only command prompt and not in export log file.
[oracle@ora19cdb dump_bkp1]$ expdp directory=dump_bkp1 dumpfile=hassan_sch2.dmp logfile=hassan_sch2.log schemas=hassan LOGTIME=STATUS Export: Release 19.0.0.0.0 - Production on Wed Nov 24 09:08:56 2024 Version 19.18.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 24-NOV-24 09:09:00.524: Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA directory=dump_bkp1 dumpfile=hassan_sch2.dmp logfile=hassan_sch2.log schemas=hassan LOGTIME=STATUS 24-NOV-24 09:09:02.394: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA 24-NOV-24 09:09:02.644: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS 24-NOV-24 09:09:03.247: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 24-NOV-24 09:09:03.783: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER 24-NOV-24 09:09:03.991: Processing object type SCHEMA_EXPORT/USER 24-NOV-24 09:09:04.088: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT 24-NOV-24 09:09:04.180: Processing object type SCHEMA_EXPORT/ROLE_GRANT 24-NOV-24 09:09:04.271: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE 24-NOV-24 09:09:04.624: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA 24-NOV-24 09:09:07.478: Processing object type SCHEMA_EXPORT/TABLE/TABLE 24-NOV-24 09:09:10.459: Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE 24-NOV-24 09:09:11.202: Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE 24-NOV-24 09:09:15.061: Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX 24-NOV-24 09:09:22.588: . . exported "HASSAN"."BOWIE" 688.8 KB 10000 rows 24-NOV-24 09:09:22.590: . . exported "HASSAN"."ADD_PICTURE" 0 KB 0 rows 24-NOV-24 09:09:22.622: . . exported "HASSAN"."T1" 5.632 KB 9 rows 24-NOV-24 09:09:22.652: . . exported "HASSAN"."TEST1" 5.125 KB 4 rows 24-NOV-24 09:09:22.683: . . exported "HASSAN"."TEST2" 5.109 KB 2 rows 24-NOV-24 09:09:24.805: Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded 24-NOV-24 09:09:24.882: ****************************************************************************** 24-NOV-24 09:09:24.883: Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: 24-NOV-24 09:09:24.885: /u02/backup/dump_bkp1/hassan_sch2.dmp 24-NOV-24 09:09:25.246: Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Nov 24 09:09:24 2024 elapsed 0 00:00:25