expdp中跟踪每一个步骤的时间——metrics=y

联系:QQ(5163721)

标题:expdp中跟踪每一个步骤的时间——metrics=y

作者:Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

在12c中,我们知道expdp和impdp可以看到每一步的时间,其实这个功能在11.2中就可以了,需要加一个未公开的参数 metrics=y,例如:

[oracle@lunar datapump]$ expdp lunar/lunar DIRECTORY=lunar_dir DUMPFILE=lunar_expdp_f%U.dmp LOGFILE=lunar_expdp_f.log FULL=y PARALLEL=2 metrics=y 

Export: Release 11.2.0.3.0 - Production on Tue Mar 4 06:01:40 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Real Application Testing options
Starting "LUNAR"."SYS_EXPORT_FULL_01":  lunar/******** DIRECTORY=lunar_dir DUMPFILE=lunar_expdp_f%U.dmp LOGFILE=lunar_expdp_f.log FULL=y PARALLEL=2 metrics=y 
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 49.93 MB
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31693: Table data object "LUNAR"."FF" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01578: ORACLE data block corrupted (file # 4, block # 129)
ORA-01110: data file 4: '/stage/travel/users01.dbf'
. . exported "LUNAR"."LUNAR_PAR_TEST":"SYS_P80"."SYS_SUBP66"  13.94 KB       1 rows
     Completed 4 TABLESPACE objects in 1 seconds
Processing object type DATABASE_EXPORT/PROFILE
     Completed 1 PROFILE objects in 0 seconds
Processing object type DATABASE_EXPORT/SYS_USER/USER
     Completed 1 USER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/USER
     Completed 5 USER objects in 0 seconds
Processing object type DATABASE_EXPORT/ROLE
. . exported "SYSMAN"."MGMT_MESSAGES"                    4.156 MB   23311 rows
. . exported "LUNAR"."T"                                 1.594 MB   17632 rows
     Completed 18 ROLE objects in 1 seconds
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
     Completed 4 PROC_SYSTEM_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
     Completed 41 SYSTEM_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
........
省略部分内容
........
. . exported "SYSMAN"."MGMT_LICENSE_DEFINITIONS"         54.65 KB      59 rows
. . exported "SYSMAN"."MGMT_METRICS_1HOUR"               10.56 KB      34 rows
. . exported "SYSMAN"."MGMT_POLICY_ASSOC"                34.06 KB     358 rows
. . exported "SYSMAN"."MGMT_POLICY_VIOL_CTXT_DEF"        68.09 KB     642 rows
. . exported "SYSMAN"."MGMT_SYSTEM_PERFORMANCE_LOG"      19.38 KB     185 rows
     Completed 8 SYNONYM objects in 5 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
     Completed 673 TYPE objects in 2 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 1 OBJECT_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
     Completed 4 PROCACT_SYSTEM objects in 1 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
     Completed 17 PROCOBJ objects in 1 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
. . exported "SYSMAN"."MGMT_TASK_QTABLE"                 21.61 KB      27 rows
. . exported "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_S"         10.75 KB       1 rows
. . exported "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_S"         10.75 KB       1 rows
. . exported "SYSMAN"."EMDW_TRACE_CONFIG"                7.054 KB       9 rows
. . exported "SYSMAN"."EM_PAGE_CONDITION_METADATA"       5.640 KB       7 rows
........
省略部分内容
........
. . exported "SYSMAN"."MGMT_ARU_PRODUCTS"                34.71 KB     744 rows
     Completed 4 PROCACT_SYSTEM objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
. . exported "SYSMAN"."MGMT_ARU_RELEASES"                19.89 KB     863 rows
. . exported "SYSMAN"."MGMT_AUDIT_DESTINATION"           5.492 KB       1 rows
. . exported "SYSMAN"."MGMT_AUDIT_MASTER"                5.070 KB       1 rows
. . exported "SYSMAN"."MGMT_AVAILABILITY"                6.679 KB       1 rows
. . exported "SYSMAN"."MGMT_AVAILABILITY_MARKER"         5.875 KB       1 rows
. . exported "SYSMAN"."MGMT_AVAILABLE_SEARCHES"          7.023 KB      15 rows
. . exported "SYSMAN"."MGMT_BLACKOUT_PROXY_TARGETS"      5.054 KB       1 rows
. . exported "SYSMAN"."MGMT_BLACKOUT_REASON"             9.617 KB      60 rows
. . exported "SYSMAN"."MGMT_BSLN_METRICS"                7.351 KB       6 rows
........
省略部分内容
........
. . exported "SYSMAN"."MGMT_PAF_JOBTYPES"                10.17 KB       2 rows
. . exported "SYSMAN"."MGMT_PAF_JOBTYPE_PARAMS"          14.94 KB      16 rows
     Completed 7 PROCACT_SCHEMA objects in 5 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
. . exported "SYSMAN"."MGMT_PAF_PARAM_GROUPS"            10.79 KB       6 rows
. . exported "SYSMAN"."MGMT_PAF_PROCEDURES"              13.58 KB       2 rows
. . exported "SYSMAN"."MGMT_PAF_TEXTUAL_DATA"            24.24 KB       2 rows
. . exported "SYSMAN"."MGMT_PARAMETERS"                  13.77 KB      84 rows
.........
省略部分
.........
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS"             0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS"          0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES"             0 KB       0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rows
     Completed 773 TABLE objects in 26 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION
     Completed 6 PRE_TABLE_ACTION objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 25 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
     Completed 1045 COMMENT objects in 3 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
     Completed 194 PACKAGE objects in 2 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 1 OBJECT_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
     Completed 12 FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
     Completed 7 PROCEDURE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 1 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
     Completed 12 ALTER_FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
     Completed 7 ALTER_PROCEDURE objects in 9 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
     Completed 979 INDEX objects in 5 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
     Completed 2 INDEX objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
     Completed 685 CONSTRAINT objects in 5 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Completed 1033 INDEX_STATISTICS objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
     Completed 2 INDEX_STATISTICS objects in 2 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
     Completed 477 VIEW objects in 2 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 252 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
     Completed 68 COMMENT objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
     Completed 194 PACKAGE_BODY objects in 24 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY
     Completed 49 TYPE_BODY objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 286 REF_CONSTRAINT objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
     Completed 762 TABLE_STATISTICS objects in 6 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION
     Completed 4 POST_TABLE_ACTION objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
     Completed 81 TRIGGER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER
     Completed 17 TRIGGER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/EVENT/TRIGGER
     Completed 1 TRIGGER objects in 4 seconds
Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW
     Completed 1 MATERIALIZED_VIEW objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/JOB
     Completed 1 JOB objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
     Completed 18 PROCACT_INSTANCE objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
     Completed 18 PROCDEPOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
     Completed 6 PROCOBJ objects in 3 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
     Completed 3 PROCACT_SCHEMA objects in 0 seconds
Processing object type DATABASE_EXPORT/AUDIT
     Completed 28 AUDIT objects in 0 seconds
Master table "LUNAR"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for LUNAR.SYS_EXPORT_FULL_01 is:
  /home/oracle/test/datapump/lunar_expdp_f01.dmp
  /home/oracle/test/datapump/lunar_expdp_f02.dmp
Job "LUNAR"."SYS_EXPORT_FULL_01" completed with 1 error(s) at 06:04:09

[oracle@lunar datapump]$ 

看一下各个部分执行所花费的时间:

[oracle@lunar datapump]$ cat lunar_expdp_f.log |grep "ompleted"
     Completed 4 TABLESPACE objects in 1 seconds
     Completed 1 PROFILE objects in 0 seconds
     Completed 1 USER objects in 0 seconds
     Completed 5 USER objects in 0 seconds
     Completed 18 ROLE objects in 1 seconds
     Completed 4 PROC_SYSTEM_GRANT objects in 0 seconds
     Completed 41 SYSTEM_GRANT objects in 0 seconds
     Completed 54 ROLE_GRANT objects in 0 seconds
     Completed 5 DEFAULT_ROLE objects in 0 seconds
     Completed 1 TABLESPACE_QUOTA objects in 0 seconds
     Completed 1 RESOURCE_COST objects in 0 seconds
     Completed 1 TRUSTED_DB_LINK objects in 0 seconds
     Completed 30 SEQUENCE objects in 1 seconds
     Completed 5 DIRECTORY objects in 0 seconds
     Completed 12 OBJECT_GRANT objects in 0 seconds
     Completed 6 CONTEXT objects in 0 seconds
     Completed 327 SYNONYM objects in 1 seconds
     Completed 8 SYNONYM objects in 5 seconds
     Completed 673 TYPE objects in 2 seconds
     Completed 1 OBJECT_GRANT objects in 1 seconds
     Completed 4 PROCACT_SYSTEM objects in 1 seconds
     Completed 17 PROCOBJ objects in 1 seconds
     Completed 4 PROCACT_SYSTEM objects in 0 seconds
     Completed 7 PROCACT_SCHEMA objects in 5 seconds
     Completed 773 TABLE objects in 26 seconds
     Completed 6 PRE_TABLE_ACTION objects in 0 seconds
     Completed 25 OBJECT_GRANT objects in 0 seconds
     Completed 1045 COMMENT objects in 3 seconds
     Completed 194 PACKAGE objects in 2 seconds
     Completed 1 OBJECT_GRANT objects in 1 seconds
     Completed 12 FUNCTION objects in 0 seconds
     Completed 7 PROCEDURE objects in 0 seconds
     Completed 1 OBJECT_GRANT objects in 0 seconds
     Completed 12 ALTER_FUNCTION objects in 0 seconds
     Completed 7 ALTER_PROCEDURE objects in 9 seconds
     Completed 979 INDEX objects in 5 seconds
     Completed 2 INDEX objects in 1 seconds
     Completed 685 CONSTRAINT objects in 5 seconds
     Completed 1033 INDEX_STATISTICS objects in 1 seconds
     Completed 2 INDEX_STATISTICS objects in 2 seconds
     Completed 477 VIEW objects in 2 seconds
     Completed 252 OBJECT_GRANT objects in 0 seconds
     Completed 68 COMMENT objects in 1 seconds
     Completed 194 PACKAGE_BODY objects in 24 seconds
     Completed 49 TYPE_BODY objects in 0 seconds
     Completed 286 REF_CONSTRAINT objects in 1 seconds
     Completed 762 TABLE_STATISTICS objects in 6 seconds
     Completed 4 POST_TABLE_ACTION objects in 1 seconds
     Completed 81 TRIGGER objects in 0 seconds
     Completed 17 TRIGGER objects in 0 seconds
     Completed 1 TRIGGER objects in 4 seconds
     Completed 1 MATERIALIZED_VIEW objects in 0 seconds
     Completed 1 JOB objects in 0 seconds
     Completed 18 PROCACT_INSTANCE objects in 1 seconds
     Completed 18 PROCDEPOBJ objects in 0 seconds
     Completed 6 PROCOBJ objects in 3 seconds
     Completed 3 PROCACT_SCHEMA objects in 0 seconds
     Completed 28 AUDIT objects in 0 seconds
Job "LUNAR"."SYS_EXPORT_FULL_01" completed with 1 error(s) at 06:04:09
[oracle@lunar datapump]$ 
此条目发表在 expdp/impdp 分类目录,贴了 , 标签。将固定链接加入收藏夹。

expdp中跟踪每一个步骤的时间——metrics=y》有 1 条评论

  1. Luke 说:

    expdp的时候,经常在estimate阶段花费很长时间。有什么办法/隐含参数可以跳过或加速这个阶段吗?

Luke 进行回复 取消回复

电子邮件地址不会被公开。 必填项已用 * 标注