ITPub博客

首页 > 数据库 > Oracle > [20200620]expdp impdp exclude参数.txt

[20200620]expdp impdp exclude参数.txt

原创 Oracle 作者:lfree 时间:2020-06-20 11:39:13 0 删除 编辑

[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

--//放弃,许多不理解.


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2699599/,如需转载,请注明出处,否则将追究法律责任。

全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2703
  • 访问量
    6494744