[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
--//放棄,許多不理解.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2699599/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20200620]IMPDP TRANSFORM引數再探究.txtORM
- expdp/impdp 詳細引數解釋
- impdp和expdp用法及引數介紹
- expdp/impdp變慢 (Doc ID 2469587.1)
- 【Data Pump】expdp/impdp Job基本管理
- oracle資料庫的impdp，expdpOracle資料庫
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- ORACLE EXPDP IMPDP 的停止和啟動及監控Oracle
- EXP、IMP、SQLLOADER、EXPDP、IMPDP、DBMS_METADATA、SQLPLUS等方面SQL
- Oracle 12c expdp和impdp匯出匯入表Oracle
- 使用資料泵（expdp、impdp）遷移資料庫流程資料庫
- Oracle 10g expdp attach引數體驗Oracle 10g
- 資料泵datapump（expdp/impdp）的parfile用法，避免跳脫字元字元
- 使用impdp,expdp資料泵進入海量資料遷移
- [20200309]expdp 與read only.txt
- [20180628]expdp與rows=n.txt
- [20180413]bash 位置引數.txt
- oracle dblink用法總結和expdp和impdp利用dblink倒入匯出到本地Oracle
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- 【Data Pump】理解expdp中的ESTIMATE和ESTIMATE_ONLY引數
- [20231212]impdp content=metadata_only locks the stats.txt
- 【Datapump】Oracle資料泵遷移資料命令參考(expdp/impdp說明）Oracle
- [20210826]核心引數kernel.sem.txt
- [20190917]oracle引數deferred屬性.txtOracle
- [20200220]windows設定keepalive引數.txtWindows
- [20210209]修改CPU_COUNT引數.txt
- Linux下執行資料泵expdp和impdp命令，字元轉義案例兩則Linux字元
- exp和expdp的filesize引數的使用--匯出多個檔案
- [20220913]hugepage相關引數含義.txt
- [20191204]hugepage相關引數含義.txt
- [20190417]隱含引數_SPIN_COUNT.txt
- [20210310]db_lost_write_protect引數.txt
- [20190409]latch get 引數where and why.txt
- [20180308]測試ARG_MAX引數.txt
- Datapump：EXCLUDE/INCLUDE
- [20200904]12c invisible column impdp segment_column_id.txt
- [20231109]bbed p命令dba引數問題.txt
- [20211027]引數plscope_settings分析PLSQL.txtSQL