本篇內(nèi)容介紹了“數(shù)據(jù)庫(kù)中數(shù)據(jù)泵避免個(gè)別表數(shù)據(jù)的導(dǎo)出方法是什么”的有關(guān)知識(shí),在實(shí)際案例的操作過(guò)程中,不少人都會(huì)遇到這樣的困境,接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧!希望大家仔細(xì)閱讀,能夠?qū)W有所成!
創(chuàng)新互聯(lián)是專業(yè)的博興網(wǎng)站建設(shè)公司,博興接單;提供成都做網(wǎng)站、網(wǎng)站設(shè)計(jì),網(wǎng)頁(yè)設(shè)計(jì),網(wǎng)站設(shè)計(jì),建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行博興網(wǎng)站開發(fā)網(wǎng)頁(yè)制作和功能擴(kuò)展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團(tuán)隊(duì),希望更多企業(yè)前來(lái)合作!
對(duì)于數(shù)據(jù)泵EXPDP/IMPDP而言,功能比普通EXP/IMP功能要強(qiáng)的多,因此也可以實(shí)現(xiàn)一些普通導(dǎo)出導(dǎo)入工具很難完成的工作。
比如今天碰到的這個(gè)問(wèn)題,要導(dǎo)出一些表,但是其中個(gè)別表只導(dǎo)出結(jié)構(gòu)而不導(dǎo)出數(shù)據(jù)。
SQL> conn test/test
Connected.
SQL> set pages 100 lines 120
SQL> select count(*) from t;
COUNT(*)
----------
23
SQL> select count(*) from tt;
COUNT(*)
----------
72
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t_tt1.dp tables=(t,tt)
Export: Release 10.2.0.3.0 - 64bit Production on星期二, 25 8月, 2009 16:04:58
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=d_output dumpfile=t_tt1.dp tables=(t,tt)
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."T" 5.953 KB 23 rows
. . exported "TEST"."TT" 6.421 KB 72 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/home/oracle/t_tt1.dp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 16:05:02
用T和TT表作為例子,分別代表需要導(dǎo)出結(jié)構(gòu)的表和同時(shí)包含結(jié)構(gòu)和數(shù)據(jù)的表。
這個(gè)需求對(duì)于普通的EXP/IMP來(lái)說(shuō),只能通過(guò)兩次導(dǎo)出操作來(lái)完成,一次導(dǎo)出包含數(shù)據(jù)的表,另一個(gè)通過(guò)執(zhí)行ROWS=N導(dǎo)出僅需要結(jié)構(gòu)的表。
對(duì)于EXPDP來(lái)說(shuō),同樣可以使用類似的方法,參數(shù)CONTENT控制導(dǎo)出的結(jié)構(gòu)、數(shù)據(jù)還是全部:
[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t_tt2.dp tables=(t,tt) content=metadata_only
Export: Release 10.2.0.3.0 - 64bit Production on星期二, 25 8月, 2009 16:32:59
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=d_output dumpfile=t_tt2.dp tables=(t,tt) content=metadata_only
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/home/oracle/t_tt2.dp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 16:33:02
但是這種方法控制的是整體,現(xiàn)在需要對(duì)其中的個(gè)別對(duì)象只導(dǎo)出表結(jié)構(gòu)。最好想到的方法是通過(guò)QUERY來(lái)控制:
[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t_tt3.dp tables=(t,tt) query='t:"where 1=2"'
Export: Release 10.2.0.3.0 - 64bit Production on星期二, 25 8月, 2009 16:51:37
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=d_output dumpfile=t_tt3.dp tables=(t,tt) query=t:"where 1=2"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."T" 5.507 KB 0 rows
. . exported "TEST"."TT" 6.421 KB 72 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/home/oracle/t_tt.dp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 16:51:41
通過(guò)對(duì)T表添加一個(gè)恒為FALSE的查詢條件,使得T表導(dǎo)出的時(shí)候獲取0條記錄,從而達(dá)到只導(dǎo)T的結(jié)構(gòu)的目的。
但是這種方法對(duì)于數(shù)據(jù)量比較大的表效率會(huì)比較低,因?yàn)镺racle會(huì)進(jìn)行導(dǎo)出的操作,只是在處理的時(shí)候?qū)⒂涗涍^(guò)濾掉,除了沒(méi)有將數(shù)據(jù)寫到導(dǎo)出文件,其他所有的操作都進(jìn)行了,因此效率很低。
而實(shí)際上,數(shù)據(jù)泵還有更好的辦法來(lái)解決這個(gè)問(wèn)題:使用EXCLUDE參數(shù)。
[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t_tt4.dp tables=(t,tt) exclude=table/table_data:\"=\'T\'\"
Export: Release 10.2.0.3.0 - 64bit Production on星期二, 25 8月, 2009 16:59:39
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=d_output dumpfile=t_tt4.dp tables=(t,tt) exclude=table/table_data:"='T'"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."TT" 6.421 KB 72 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/home/oracle/t_tt4.dp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 16:59:43
這里看不到T表的信息,下面檢查一下導(dǎo)出是否生效:
[oracle@yans1 ~]$ sqlplus test/test
SQL*Plus: Release 10.2.0.3.0 - Production on星期二 8月 25 17:00:27 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME NOT NULL VARCHAR2(30)
TYPE VARCHAR2(7)
SQL> drop table t;
Table dropped.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@yans1 ~]$ impdp test/test directory=d_output dumpfile=t_tt4.dp tables=t
Import: Release 10.2.0.3.0 - 64bit Production on星期二, 25 8月, 2009 17:00:41
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01": test/******** directory=d_output dumpfile=t_tt4.dp tables=t
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at 17:00:43
[oracle@yans1 ~]$ sqlplus test/test
SQL*Plus: Release 10.2.0.3.0 - Production on星期二 8月 25 17:00:47 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME NOT NULL VARCHAR2(30)
TYPE VARCHAR2(7)
SQL> select * from t;
no rows selected
很顯然,利用EXCLUDE的方式使得數(shù)據(jù)泵導(dǎo)出的時(shí)候去掉了T表的數(shù)據(jù)。
“數(shù)據(jù)庫(kù)中數(shù)據(jù)泵避免個(gè)別表數(shù)據(jù)的導(dǎo)出方法是什么”的內(nèi)容就介紹到這里了,感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站,小編將為大家輸出更多高質(zhì)量的實(shí)用文章!
網(wǎng)頁(yè)題目:數(shù)據(jù)庫(kù)中數(shù)據(jù)泵避免個(gè)別表數(shù)據(jù)的導(dǎo)出方法是什么
網(wǎng)頁(yè)地址:http://muchs.cn/article26/ghipjg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供網(wǎng)站排名、網(wǎng)站營(yíng)銷、ChatGPT、定制網(wǎng)站、外貿(mào)網(wǎng)站建設(shè)、網(wǎng)站維護(hù)
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來(lái)源: 創(chuàng)新互聯(lián)