Oracle 12C新特性-数据泵新参数(LOGTIME)
https://docs.oracle.com/database/121/SUTIL/GUID-56B6B4EA-5A2B-45B8-9B41-8966378C2C3D.htm#SUTIL4281
Home / Oracle Database Online Documentation Library, 12c Release 1 (12.1.0.2)
LOGTIME
Default: No timestamps are recorded
Purpose
Specifies that messages displayed during export operations be timestamped. You can use the timestamps to figure out the elapsed time between different phases of a Data Pump operation. Such information can be helpful in diagnosing performance problems and estimating the timing of future similar operations.
Syntax and Description
LOGTIME=[NONE | STATUS | LOGFILE | ALL]
The available options are defined as follows:
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
1. 数据库版本
--- 数据库 19C ，相当于 12.2.0.3 版本
SQL> select banner_full from v$version;
BANNER_FULL
---------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
创建测试表和测试视图
SQL> conn cjc/cjc@cjcpdb
SQL> create table t1 as select * from dba_objects;
SQL> insert into t1 select * from t1;
72499 rows created.
SQL> /
144998 rows created.
SQL> /
289996 rows created.
SQL> update t1 set object_id=rownum;
579992 rows updated.
SQL> commit;
Commit complete.
SQL> create index i_t1_id on t1 (object_id);
SQL> create table t2 as select * from dba_objects;
SQL> create view v1_t1 as select object_id,object_name from t1;
SQL> col tname for a10
[root@cjcos ~]# mkdir /dir
[root@cjcos ~]# chown oracle.oinstall /dir
SQL> conn sys/oracle@cjcpdb as sysdba
SQL> create directory expdp_dir as '/dir';
SQL> grant read,write on directory expdp_dir to cjc;
expdp导出，添加LOGTIME=all参数，将时间信息输出到控制台和日志里
[oracle@cjcos ~]$ expdp cjc/cjc@cjcpdb owner=cjc directory=expdp_dir dumpfile=cjc.dmp logfile=cjc.log LOGTIME=all
Export: Release 19.0.0.0.0 - Production on Wed Feb 19 21:13:46 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "owner=cjc" Location: Command Line, Replaced with: "schemas=cjc"
Legacy Mode has set reuse_dumpfiles=true parameter.
19-FEB-20 21:14:26.868: Starting "CJC"."SYS_EXPORT_SCHEMA_01": cjc/********@cjcpdb schemas=cjc directory=expdp_dir dumpfile=cjc.dmp logfile=cjc.log LOGTIME=all reuse_dumpfiles=true
19-FEB-20 21:14:48.584: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
19-FEB-20 21:14:49.509: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
19-FEB-20 21:14:50.458: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
19-FEB-20 21:15:11.951: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
19-FEB-20 21:15:14.566: Processing object type SCHEMA_EXPORT/USER
19-FEB-20 21:15:14.831: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
19-FEB-20 21:15:14.977: Processing object type SCHEMA_EXPORT/ROLE_GRANT
19-FEB-20 21:15:15.098: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
19-FEB-20 21:15:17.075: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
19-FEB-20 21:15:37.295: Processing object type SCHEMA_EXPORT/TABLE/TABLE
19-FEB-20 21:16:13.613: Processing object type SCHEMA_EXPORT/TABLE/COMMENT
19-FEB-20 21:16:24.828: Processing object type SCHEMA_EXPORT/VIEW/VIEW
19-FEB-20 21:16:28.551: Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
19-FEB-20 21:16:40.518: . . exported "CJC"."T2" 9.562 MB 72502 rows
19-FEB-20 21:16:46.104: . . exported "CJC"."T1" 76.45 MB 579992 rows
19-FEB-20 21:16:54.585: Master table "CJC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
19-FEB-20 21:16:54.617: ******************************************************************************
19-FEB-20 21:16:54.618: Dump file set for CJC.SYS_EXPORT_SCHEMA_01 is:
19-FEB-20 21:16:54.626: /dir/cjc.dmp
19-FEB-20 21:16:54.686: Job "CJC"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Feb 19 21:16:54 2020 elapsed 0 00:02:43
impdp导入，添加LOGTIME=all参数，将时间信息输出到控制台和日志里
[oracle@cjcos ~]$
impdp cjc/cjc@cjcpdb remap_schema=cjc:chen remap_tablespace=cjctbs:users directory=expdp_dir dumpfile=cjc.dmp logfile=cjc01.log LOGTIME=all
Import: Release 19.0.0.0.0 - Production on Wed Feb 19 21:21:41 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
19-FEB-20 21:22:02.943: Master table "CJC"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
19-FEB-20 21:22:03.769: Starting "CJC"."SYS_IMPORT_FULL_01": cjc/********@cjcpdb remap_schema=cjc:chen remap_tablespace=cjctbs:users directory=expdp_dir dumpfile=cjc.dmp logfile=cjc01.log LOGTIME=all
19-FEB-20 21:22:03.874: Processing object type SCHEMA_EXPORT/USER
19-FEB-20 21:22:05.493: ORA-31684: Object type USER:"CHEN" already exists
19-FEB-20 21:22:05.596: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
19-FEB-20 21:22:06.000: Processing object type SCHEMA_EXPORT/ROLE_GRANT
19-FEB-20 21:22:06.233: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
19-FEB-20 21:22:06.532: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
19-FEB-20 21:22:07.924: Processing object type SCHEMA_EXPORT/TABLE/TABLE
19-FEB-20 21:22:09.211: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
19-FEB-20 21:22:11.962: . . imported "CHEN"."T2" 9.562 MB 72502 rows
19-FEB-20 21:22:17.963: . . imported "CHEN"."T1" 76.45 MB 579992 rows
19-FEB-20 21:22:18.022: Processing object type SCHEMA_EXPORT/VIEW/VIEW
19-FEB-20 21:22:18.788: Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
19-FEB-20 21:22:21.094: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
19-FEB-20 21:22:21.200: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
19-FEB-20 21:22:21.347: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
19-FEB-20 21:23:09.795: Job "CJC"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Wed Feb 19 21:23:09 2020 elapsed 0 00:01:18
不加LOGTIME参数，导出和导入如下，可以对比看下：
导出
[oracle@cjcos ~]$ expdp cjc/cjc@cjcpdb owner=cjc directory=expdp_dir dumpfile=cjc.dmp logfile=cjc.log
Export: Release 19.0.0.0.0 - Production on Wed Feb 19 21:02:57 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "owner=cjc" Location: Command Line, Replaced with: "schemas=cjc"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "CJC"."SYS_EXPORT_SCHEMA_01": cjc/********@cjcpdb schemas=cjc directory=expdp_dir dumpfile=cjc.dmp logfile=cjc.log reuse_dumpfiles=true
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/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
. . exported "CJC"."T2" 9.562 MB 72502 rows
. . exported "CJC"."T1" 76.45 MB 579992 rows
Master table "CJC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CJC.SYS_EXPORT_SCHEMA_01 is:
/dir/cjc.dmp
Job "CJC"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Feb 19 21:06:26 2020 elapsed 0 00:02:52
导入
[oracle@cjcos ~]$ impdp cjc/cjc@cjcpdb remap_schema=cjc:chen remap_tablespace=cjctbs:users directory=expdp_dir dumpfile=cjc.dmp logfile=cjc01.log
Import: Release 19.0.0.0.0 - Production on Wed Feb 19 21:08:07 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "CJC"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "CJC"."SYS_IMPORT_FULL_01": cjc/********@cjcpdb remap_schema=cjc:chen remap_tablespace=cjctbs:users directory=expdp_dir dumpfile=cjc.dmp logfile=cjc01.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"CHEN" already exists
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/TABLE/TABLE_DATA
. . imported "CHEN"."T2" 9.562 MB 72502 rows
. . imported "CHEN"."T1" 76.45 MB 579992 rows
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
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
Job "CJC"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Wed Feb 19 21:09:54 2020 elapsed 0 00:01:40
欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！
