[20200620]expdp impdp exclude参数.txt

[20200620]expdp impdp exclude参数.txt --//上午在家做导入导出impdp TRANSFORM参数测试,测试exclude参数我发现一个我不理解的意思,简单记录如下: 1.环境: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 2.测试: d:\tmp> expdp scott/btbtms@test01p DIRECTORY=TMP_EXPDP dumpfile=empx.dmp logfile=empx.log tables=scott.empx CONTENT=METADATA_ONLY Export: Release 12.2.0.1.0 - Production on Sat Jun 20 09:26:42 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a**@test01p DIRECTORY=TMP_EXPDP dumpfile=empx.dmp logfile=empx.log tables=scott.empx CONTENT=METADATA_ONLY Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:   D:\TMP\EXPDP\EMPX.DMP Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jun 20 09:27:34 2020 elapsed 0 00:00:48 --//感觉expdp处理的顺序有点不理解.为什么先处理统计信息(STATISTICS).然后才是导出TABLE,INDEX. --//按照我的理解导出时应该是TABLE,INDEX.然后才是统计信息.在看看导入: d:\tmp> impdp scott/btbtms@test01p  DIRECTORY=TMP_EXPDP dumpfile=empx.dmp logfile=empx1.log full=y transform=SEGMENT_ATTRIBUTES:n SQLFILE=empx.txt Import: Release 12.2.0.1.0 - Production on Sat Jun 20 09:30:21 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Master table "SCOTT"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded Starting "SCOTT"."SYS_SQL_FILE_FULL_01":  scott/a**@test01p DIRECTORY=TMP_EXPDP dumpfile=empx.dmp logfile=empx1.log full=y transform=SEGMENT_ATTRIBUTES:n SQLFILE=empx.txt Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "SCOTT"."SYS_SQL_FILE_FULL_01" successfully completed at Sat Jun 20 09:30:34 2020 elapsed 0 00:00:10 --//可以发现导入时先处理table,index,然后STATISTICS.最后在统计里面有1个步骤TABLE_EXPORT/TABLE/STATISTICS/MARKER有表示什么? --//词霸查询结果: marker    搜索网络 英 [?mɑ:k?(r)]   美 [?mɑrk?(r)]   n.  标识，标记; 记号笔，阅卷人; 防守队员; 特征; --//这样建立的导出dmp文件在导入时不是不能顺序读取,还有会过头来再读取统计信息来导入吗?oracle为什么要这样设计导出. 3.继续探究: --//删除前面建立的dp文件以及对应日志.并且分析表empx,过程略. d:\tmp\expdp> expdp scott/btbtms@test01p DIRECTORY=TMP_EXPDP dumpfile=empy.dp logfile=empy.log tables=scott.empx CONTENT=METADATA_ONLY  exclude=MARKER expdp scott/btbtms@test01p DIRECTORY=TMP_EXPDP dumpfile=empy.dp logfile=empy.log tables=scott.empx CONTENT=METADATA_ONLY  exclude=MARKER Export: Release 12.2.0.1.0 - Production on Sat Jun 20 10:58:43 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a**@test01p DIRECTORY=TMP_EXPDP dumpfile=empy.dp logfile=empy.log tables=scott.empx CONTENT=METADATA_ONLY exclude=MARKER Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:   D:\TMP\EXPDP\EMPY.DP Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jun 20 10:59:00 2020 elapsed 0 00:00:15 --//注意排除了exclude=MARKER. SCOTT@test01p> rename empx to empy; Table renamed. SCOTT@test01p> alter index pk_empx rename to pk_empy; Index altered. d:\tmp\expdp> impdp scott/btbtms@test01p  DIRECTORY=TMP_EXPDP dumpfile=empy.dp logfile=empx1.log full=y  exclude=MARKER Import: Release 12.2.0.1.0 - Production on Sat Jun 20 11:10:20 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production ORA-39002: invalid operation ORA-39168: Object path MARKER was not found. --//报错,找不到Object path MARKER. d:\tmp\expdp> impdp scott/btbtms@test01p  DIRECTORY=TMP_EXPDP dumpfile=empy.dp logfile=empx1.log full=y Import: Release 12.2.0.1.0 - Production on Sat Jun 20 11:11:38 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/a**@test01p DIRECTORY=TMP_EXPDP dumpfile=empy.dp logfile=empx1.log full=y Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at Sat Jun 20 11:11:46 2020 elapsed 0 00:00:06 --//没有Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER这个步骤. SCOTT@test01p> @ tab_lh scott empx '' DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT   OWNER TABLE_NAME COLUMN SAMPLE  : @ TAB_LH TABLE_NAME [COLUMN_NAME] IF NOT INPUT COLUMN_NAME ,USE "" . COLUMN_NAME DATA_TYPE DATA_LENGTH N NUM_DISTINCT    DENSITY SAMPLE_SIZE TRANS_LOW TRANS_HIGH  NUM_NULLS NUM_BUCKETS LAST_ANALYZED HISTOGRAM DATA_DEFAULT ----------- --------- ----------- - ------------ ---------- ----------- --------- ---------- ---------- ----------- ------------- --------- ------------ EMPNO       NUMBER             22 Y                                                                                               NONE ENAME       VARCHAR2           10 Y                                                                                               NONE JOB         VARCHAR2            9 Y                                                                                               NONE MGR         NUMBER             22 Y                                                                                               NONE HIREDATE    DATE                7 Y                                     -- ::     -- ::                                           NONE SAL         NUMBER             22 Y                                                                                               NONE COMM        NUMBER             22 Y                                                                                               NONE DEPTNO      NUMBER             22 Y                                                                                               NONE 8 rows selected. --//没有统计信息导入. 4.重新测试: --//删除垃圾表,修改会原来表名. d:\tmp\expdp> expdp scott/btbtms@test01p DIRECTORY=TMP_EXPDP dumpfile=empx.dp logfile=empx.log tables=scott.empx CONTENT=METADATA_ONLY Export: Release 12.2.0.1.0 - Production on Sat Jun 20 11:24:24 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a*@test01p DIRECTORY=TMP_EXPDP dumpfile=empx.dp logfile=empx.log tables=scott.empx CONTENT=METADATA_ONLY Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:   D:\TMP\EXPDP\EMPX.DP Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jun 20 11:24:50 2020 elapsed 0 00:00:24 SCOTT@test01p> rename empx to empy; Table renamed. SCOTT@test01p> alter index pk_empx rename to pk_empy; Index altered. d:\tmp\expdp> impdp scott/btbtms@test01p  DIRECTORY=TMP_EXPDP dumpfile=empx.dp logfile=empx1.log full=y  exclude=MARKER Import: Release 12.2.0.1.0 - Production on Sat Jun 20 11:26:25 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/a*@test01p DIRECTORY=TMP_EXPDP dumpfile=empx.dp logfile=empx1.log full=y exclude=MARKER Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at Sat Jun 20 11:26:57 2020 elapsed 0 00:00:30 --//并不能单独排除exclude=MARKER这个步骤. SCOTT@test01p> select * from empx; no rows selected SCOTT@test01p> @ tab_lh scott empx '' DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT   OWNER TABLE_NAME COLUMN SAMPLE  : @ TAB_LH TABLE_NAME [COLUMN_NAME] IF NOT INPUT COLUMN_NAME ,USE "" . COLUMN_NAME DATA_TYPE DATA_LENGTH N NUM_DISTINCT    DENSITY SAMPLE_SIZE TRANS_LOW           TRANS_HIGH           NUM_NULLS NUM_BUCKETS LAST_ANALYZED       HISTOGRAM    DATA_DEFAULT ----------- --------- ----------- - ------------ ---------- ----------- ------------------- ------------------- ---------- ----------- ------------------- ------------ ------------ EMPNO       NUMBER             22 Y           14 .071428571          14 7369                7934                         0           1 2020-06-20 11:24:14 NONE ENAME       VARCHAR2           10 Y           14 .071428571          14 ADAMS               WARD                         0           1 2020-06-20 11:24:14 NONE JOB         VARCHAR2            9 Y            5         .2          14 ANALYST             SALESMAN                     0           1 2020-06-20 11:24:14 NONE MGR         NUMBER             22 Y            6 .166666667          13 7566                7902                         1           1 2020-06-20 11:24:14 NONE HIREDATE    DATE                7 Y           13 .076923077          14 1980-12-17 00:00:00 1987-05-23 00:00:00          0           1 2020-06-20 11:24:14 NONE SAL         NUMBER             22 Y           12 .083333333          14 800                 5000                         0           1 2020-06-20 11:24:14 NONE COMM        NUMBER             22 Y            4        .25           4 0                   1400                        10           1 2020-06-20 11:24:14 NONE DEPTNO      NUMBER             22 Y            3 .333333333          14 10                  30                           0           1 2020-06-20 11:24:14 NONE 8 rows selected. --// 这样操作有统计信息,但是MARKER的作用不理解. --//这样讲expdp 的exclude参数应该也支持TABLE_STATISTICS,INDEX_STATISTICS之类的步骤. d:\tmp\expdp> expdp scott/btbtms@test01p DIRECTORY=TMP_EXPDP dumpfile=empy.dp logfile=empx.log tables=scott.empy CONTENT=METADATA_ONLY exclude=TABLE_STATISTICS,INDEX_STATISTICS Export: Release 12.2.0.1.0 - Production on Sat Jun 20 11:36:10 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a**@test01p DIRECTORY=TMP_EXPDP dumpfile=empy.dp logfile=empx.log tables=scott.empy CONTENT=METADATA_ONLY exclude=TABLE_STATISTICS,INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:   D:\TMP\EXPDP\EMPY.DP Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jun 20 11:36:28 2020 elapsed 0 00:00:16 --//放弃,许多不理解.