20200219-Oracle 12C新特性-数据泵新参数(LOGTIME)

最新推荐文章于 2025-05-12 17:06:58 发布

原创最新推荐文章于 2025-05-12 17:06:58 发布 · 246 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

在这里插入图片描述

数据库版本

数据库 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

参考:

 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

欢迎关注我的公众号:IT小Chen