[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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2699599/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
