CNDBA社区
概念
The new LOGTIME command-line parameter available in Oracle Data Pump Export and Import allows you to request that messages displayed during export and import operations be timestamped. The valid values are:
NONE - no timestamps on status or log file messages (same as the default)
STATUS - timestamps on status messages only
LOGFILE - timestamps on log file messages only
ALL - timestamps on both status and log file messages
There is also a new option for the DBMS_DATAPUMP.SET_PARAMETER procedure called LOGTIME and the valid values are the same.
You can use the timestamps to know the elapsed time between different parts of an Oracle Data Pump operation, which can be helpful in diagnosing performance problems and in estimating the timing of similar operations in the future.
在Oracle数据泵导出和导入中可用的新的LOGTIME命令行参数允许您请求在导出和导入操作期间显示消息的时间戳。有效的值是
NONE –状态或日志文件消息没有时间戳(与默认值相同)
STATUS – 只在状态消息上使用时间戳
LOGFILE - 只在日志文件消息上使用时间戳
ALL - 状态和日志文件消息都使用时间戳
DBMS_DATAPUMP.SET_PARAMETER存储过程也有一个新选项LOGTIME和有效值是相同的。
您可以使用时间戳来了解Oracle数据泵操作不同部分之间的运行时间，这有助于诊断性能问题，并估计将来类似操作的时间。
实验
1）参数LOGTIME=ALL，控制台和日志记录都有时间戳
[oracle@host1 ~]$ expdp test/test@pdbcndba DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp logfile=expdat.log SCHEMAS=test LOGTIME=ALL
Export: Release 12.1.0.2.0 - Production on Fri Aug 4 14:10:37 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
04-AUG-17 14:10:38.787: Starting "TEST"."SYS_EXPORT_SCHEMA_01": test/********@pdbcndba DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp logfile=expdat.log SCHEMAS=test LOGTIME=ALL
04-AUG-17 14:10:39.223: Estimate in progress using BLOCKS method...
04-AUG-17 14:10:40.400: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
04-AUG-17 14:10:40.438: Total estimation using BLOCKS method: 13 MB
04-AUG-17 14:10:40.675: Processing object type SCHEMA_EXPORT/USER
04-AUG-17 14:10:40.732: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
04-AUG-17 14:10:40.751: Processing object type SCHEMA_EXPORT/ROLE_GRANT
04-AUG-17 14:10:40.774: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
04-AUG-17 14:10:40.803: Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
04-AUG-17 14:10:41.047: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
04-AUG-17 14:10:45.001: Processing object type SCHEMA_EXPORT/TABLE/TABLE
04-AUG-17 14:10:55.798: Processing object type SCHEMA_EXPORT/TABLE/COMMENT
04-AUG-17 14:10:56.667: Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
04-AUG-17 14:10:57.684: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
04-AUG-17 14:10:57.721: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
04-AUG-17 14:10:58.325: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
04-AUG-17 14:10:58.399: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
04-AUG-17 14:11:03.093: . . exported "TEST"."LEO2" 10.36 MB 90936 rows
04-AUG-17 14:11:03.462: Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
04-AUG-17 14:11:03.465: ******************************************************************************
04-AUG-17 14:11:03.468: Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
04-AUG-17 14:11:03.476: /backup/expdat.dmp
04-AUG-17 14:11:03.504: Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Aug 4 14:11:03 2017 elapsed 0 00:00:26
--查看导出日志
[root@host1 backup]# cat expdat.log
;;;
Export: Release 12.1.0.2.0 - Production on Fri Aug 4 14:10:37 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
04-AUG-17 14:10:38.787: Starting "TEST"."SYS_EXPORT_SCHEMA_01": test/********@pdbcndba DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp logfile=expdat.log SCHEMAS=test LOGTIME=ALL
04-AUG-17 14:10:39.223: Estimate in progress using BLOCKS method...
04-AUG-17 14:10:40.400: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
04-AUG-17 14:10:40.438: Total estimation using BLOCKS method: 13 MB
04-AUG-17 14:10:40.675: Processing object type SCHEMA_EXPORT/USER
04-AUG-17 14:10:40.732: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
04-AUG-17 14:10:40.751: Processing object type SCHEMA_EXPORT/ROLE_GRANT
04-AUG-17 14:10:40.774: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
04-AUG-17 14:10:40.803: Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
04-AUG-17 14:10:41.047: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
04-AUG-17 14:10:44.984: Processing object type SCHEMA_EXPORT/TABLE/TABLE
04-AUG-17 14:10:55.787: Processing object type SCHEMA_EXPORT/TABLE/COMMENT
04-AUG-17 14:10:56.667: Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
04-AUG-17 14:10:57.684: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
04-AUG-17 14:10:57.721: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
04-AUG-17 14:10:58.324: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
04-AUG-17 14:10:58.399: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
04-AUG-17 14:11:03.093: . . exported "TEST"."LEO2" 10.36 MB 90936 rows
04-AUG-17 14:11:03.462: Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
04-AUG-17 14:11:03.465: ******************************************************************************
04-AUG-17 14:11:03.468: Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
04-AUG-17 14:11:03.476: /backup/expdat.dmp
04-AUG-17 14:11:03.504: Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Aug 4 14:11:03 2017 elapsed 0 00:00:26[oracle@host1 ~]$ expdp test/test@pdbcndba DIRECTORY=dpump_dir1 DUMPFILE=expdat1.dmp logfile=expdat1.log SCHEMAS=test LOGTIME=NONE
Export: Release 12.1.0.2.0 - Production on Fri Aug 4 14:05:56 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "TEST"."SYS_EXPORT_SCHEMA_01": test/********@pdbcndba DIRECTORY=dpump_dir1 DUMPFILE=expdat1.dmp logfile=expdat1.log SCHEMAS=test LOGTIME=NONE
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13 MB
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
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
. . exported "TEST"."LEO2" 10.36 MB 90936 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
/backup/expdat1.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Aug 4 14:06:19 2017 elapsed 0 00:00:22
--查看导出日志
[root@host1 backup]# cat expdat1.log
;;;
Export: Release 12.1.0.2.0 - Production on Fri Aug 4 14:05:56 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "TEST"."SYS_EXPORT_SCHEMA_01": test/********@pdbcndba DIRECTORY=dpump_dir1 DUMPFILE=expdat1.dmp logfile=expdat1.log SCHEMAS=test LOGTIME=NONE
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13 MB
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
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
. . exported "TEST"."LEO2" 10.36 MB 90936 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
/backup/expdat1.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Aug 4 14:06:19 2017 elapsed 0 00:00:22[oracle@host1 ~]$ expdp test/test@pdbcndba DIRECTORY=dpump_dir1 DUMPFILE=expdat2.dmp logfile=expdat2.log SCHEMAS=test LOGTIME=status
Export: Release 12.1.0.2.0 - Production on Fri Aug 4 14:06:58 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
04-AUG-17 14:07:00.357: Starting "TEST"."SYS_EXPORT_SCHEMA_01": test/********@pdbcndba DIRECTORY=dpump_dir1 DUMPFILE=expdat2.dmp logfile=expdat2.log SCHEMAS=test LOGTIME=status
04-AUG-17 14:07:00.745: Estimate in progress using BLOCKS method...
04-AUG-17 14:07:01.831: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
04-AUG-17 14:07:01.868: Total estimation using BLOCKS method: 13 MB
04-AUG-17 14:07:02.102: Processing object type SCHEMA_EXPORT/USER
04-AUG-17 14:07:02.163: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
04-AUG-17 14:07:02.184: Processing object type SCHEMA_EXPORT/ROLE_GRANT
04-AUG-17 14:07:02.208: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
04-AUG-17 14:07:02.237: Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
04-AUG-17 14:07:02.496: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
04-AUG-17 14:07:06.101: Processing object type SCHEMA_EXPORT/TABLE/TABLE
04-AUG-17 14:07:14.847: Processing object type SCHEMA_EXPORT/TABLE/COMMENT
04-AUG-17 14:07:15.691: Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
04-AUG-17 14:07:16.652: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
04-AUG-17 14:07:16.682: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
04-AUG-17 14:07:17.318: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
04-AUG-17 14:07:17.339: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
04-AUG-17 14:07:21.131: . . exported "TEST"."LEO2" 10.36 MB 90936 rows
04-AUG-17 14:07:21.490: Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
04-AUG-17 14:07:21.493: ******************************************************************************
04-AUG-17 14:07:21.495: Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
04-AUG-17 14:07:21.500: /backup/expdat2.dmp
04-AUG-17 14:07:21.521: Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Aug 4 14:07:21 2017 elapsed 0 00:00:22
--查看导出日志
[root@host1 backup]# cat expdat2.log
;;;
Export: Release 12.1.0.2.0 - Production on Fri Aug 4 14:06:58 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "TEST"."SYS_EXPORT_SCHEMA_01": test/********@pdbcndba DIRECTORY=dpump_dir1 DUMPFILE=expdat2.dmp logfile=expdat2.log SCHEMAS=test LOGTIME=status
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13 MB
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
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
. . exported "TEST"."LEO2" 10.36 MB 90936 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
/backup/expdat2.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Aug 4 14:07:21 2017 elapsed 0 00:00:22[oracle@host1 ~]$ expdp test/test@pdbcndba DIRECTORY=dpump_dir1 DUMPFILE=expdat3.dmp logfile=expdat3.log SCHEMAS=test LOGTIME=logfile
Export: Release 12.1.0.2.0 - Production on Fri Aug 4 14:08:41 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "TEST"."SYS_EXPORT_SCHEMA_01": test/********@pdbcndba DIRECTORY=dpump_dir1 DUMPFILE=expdat3.dmp logfile=expdat3.log SCHEMAS=test LOGTIME=logfile
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13 MB
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
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
. . exported "TEST"."LEO2" 10.36 MB 90936 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
/backup/expdat3.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Aug 4 14:09:05 2017 elapsed 0 00:00:23
--查看导出日志
[root@host1 backup]# cat expdat3.log
;;;
Export: Release 12.1.0.2.0 - Production on Fri Aug 4 14:08:41 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
04-AUG-17 14:08:43.641: Starting "TEST"."SYS_EXPORT_SCHEMA_01": test/********@pdbcndba DIRECTORY=dpump_dir1 DUMPFILE=expdat3.dmp logfile=expdat3.log SCHEMAS=test LOGTIME=logfile
04-AUG-17 14:08:44.074: Estimate in progress using BLOCKS method...
04-AUG-17 14:08:45.115: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
04-AUG-17 14:08:45.154: Total estimation using BLOCKS method: 13 MB
04-AUG-17 14:08:45.382: Processing object type SCHEMA_EXPORT/USER
04-AUG-17 14:08:45.445: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
04-AUG-17 14:08:45.464: Processing object type SCHEMA_EXPORT/ROLE_GRANT
04-AUG-17 14:08:45.488: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
04-AUG-17 14:08:45.514: Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
04-AUG-17 14:08:45.749: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
04-AUG-17 14:08:49.382: Processing object type SCHEMA_EXPORT/TABLE/TABLE
04-AUG-17 14:08:58.903: Processing object type SCHEMA_EXPORT/TABLE/COMMENT
04-AUG-17 14:08:59.766: Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
04-AUG-17 14:09:00.732: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
04-AUG-17 14:09:00.769: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
04-AUG-17 14:09:01.364: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
04-AUG-17 14:09:01.387: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
04-AUG-17 14:09:05.557: . . exported "TEST"."LEO2" 10.36 MB 90936 rows
04-AUG-17 14:09:05.961: Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
04-AUG-17 14:09:05.963: ******************************************************************************
04-AUG-17 14:09:05.965: Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
04-AUG-17 14:09:05.971: /backup/expdat3.dmp
04-AUG-17 14:09:05.988: Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Aug 4 14:09:05 2017 elapsed 0 00:00:23impdp 命令参数LOGTIME和expdp 一样这里不一一列举了。
参考链接：
http://docs.oracle.com/database/121/SUTIL/GUID-56B6B4EA-5A2B-45B8-9B41-8966378C2C3D.htm#SUTIL4281
http://docs.oracle.com/database/121/SUTIL/GUID-602BF900-AFB3-43D6-BE86-06BFB5BAD0FE.htm#SUTIL4282
版权声明：本文为博主原创文章，未经博主允许不得转载。
