Oracle 數(shù)據(jù)泵導(dǎo)出和導(dǎo)入

1、.：.；Oracle10g數(shù)據(jù)導(dǎo)入導(dǎo)出簡介Oracle 10g引入了DATA PUMP提供的是一種基于效力器的數(shù)據(jù)提取和恢復(fù)的適用程序，DATA PUMP在體系構(gòu)造和功能上與傳統(tǒng)的EXPORT和IMPORT適用程序相比有了顯著的提升。DATA PUMP允許您停頓和重啟作業(yè)，查看運轉(zhuǎn)的作業(yè)的形狀，及對導(dǎo)入和導(dǎo)出的數(shù)據(jù)做限制。 留意：數(shù)據(jù)泵文件與傳統(tǒng)的EXP/IMP數(shù)據(jù)轉(zhuǎn)儲文件是不兼容的。以下是DATA PUMP的幾個優(yōu)點引見：1數(shù)據(jù)泵(Data Pump)的一切任務(wù)都有數(shù)據(jù)庫實例來完成，數(shù)據(jù)庫可以并行來處置這些任務(wù)，不僅可以經(jīng)過建立多個數(shù)據(jù)泵任務(wù)進程來讀/寫正在被導(dǎo)出/導(dǎo)入的數(shù)據(jù)，也可以建立并行

2、I/O效力器以更快地讀取或插入數(shù)據(jù)，從而，單進程瓶頸被徹底處理。2經(jīng)過數(shù)據(jù)泵，以前經(jīng)過EXP/IMP主要基于Client/Server的邏輯備份方式轉(zhuǎn)換為效力器端的快速備份，數(shù)據(jù)泵主要任務(wù)在效力器端，可以經(jīng)過并行方式快速裝入或卸載數(shù)據(jù)，而且可以在運轉(zhuǎn)過程中調(diào)整并行的程度，以加快或減少資源耗費。3數(shù)據(jù)泵經(jīng)過新的API來建立和管理，這些新的任務(wù)主要由DBMS_DATAPUMP來完成。新的導(dǎo)入/導(dǎo)出工具完全成為了一個客戶端運用，經(jīng)過IMPDP/EXPDP執(zhí)行的命令實踐上都是在調(diào)用Server端的API在執(zhí)行操作，所以一旦一個義務(wù)被調(diào)度或執(zhí)行，客戶端就可以退出銜接，義務(wù)會在server端繼續(xù)執(zhí)行，隨后

3、經(jīng)過客戶端適用程序從任何地方檢查義務(wù)的形狀和進展修正創(chuàng)建DIRECTORYDATA PUMP要求為將要創(chuàng)建和讀取 的數(shù)據(jù)文件及日志文件創(chuàng)建目錄，這個參數(shù)是用來定義一個目錄，前面曾經(jīng)提到數(shù)據(jù)泵主要在Server端任務(wù)，導(dǎo)出文件需求寫出到Server端本地目錄，這個DIRECTORY就是對應(yīng)的Server端的目錄。將要訪問數(shù)據(jù)泵文件的用戶必需求擁有該目錄的讀/寫權(quán)限。留意：在開場操作之前要驗證外部目錄能否存在，并且下達create directory命令的用戶需求擁有create any directory的系統(tǒng)權(quán)限。下面給出一個創(chuàng)建名為TEST_EXPDP的目錄并授予hs_user,hs_his

4、用戶訪問此目錄讀/寫權(quán)限。SQL Create directory TEST_EXPDP as /u03/expdpdump;SQL Grant read,write on directory TEST_EXPDP to hs_user,hs_his;查看數(shù)據(jù)庫中已創(chuàng)建的directory的兩個視圖：SELECT * FROM ALL_DIRECTORIES;SELECT * FROM dba_DIRECTORIES;數(shù)據(jù)泵導(dǎo)出oracleLinuxRedHat u03$ expdp system/mingyueHS2021 schemas=hs_his,hs_futures dumpfile

5、 =expdp_test.dmp logfile=expdp_test.log directory=TEST_EXPDP;Export: Release .0 - Production on 星期三, 10 11月, 2021 0:16:04Copyright (c) 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release .0 - ProductionWith the Partitioning, Oracle Lab

6、el Security, OLAP and Data Mining Scoring Engine optionsStarting SYSTEM.SYS_EXPORT_SCHEMA_01: system/*HS2021 schemas=hs_his,hs_futures dumpfile =expdp_test.dmp logfile=expdp_test.log directory=TEST_EXPDP Estimate in progress using BLOCKS method.Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATo

7、tal estimation using BLOCKS method: 1.714 GBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessi

8、ng object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS如上面的清單所顯示的只是羅列出來即將導(dǎo)出來的一切數(shù)據(jù)庫對象，在導(dǎo)出過程中，DATA DUMP創(chuàng)建并運用了一個名為SYS_EXPORT_SCHEMA_01的對象，此對象就是DATA DUMP導(dǎo)出過程中所用的JOB名字，假設(shè)在執(zhí)行這個命令時假設(shè)沒有指定導(dǎo)出的JOB

9、名字那么就會產(chǎn)生一個默許的JOB名字，假設(shè)在導(dǎo)出過程中指定JOB名字就為以指定名字出現(xiàn)，如下：oracleLinuxRedHat u03$ expdp system/mingyueHS2021 schemas=hs_his dumpfile =expdp_test.dmp logfile=expdp_test.log directory=TEST_EXPDP job_name=hs_hisjob;Export: Release .0 - Production on 星期三, 10 11月, 2021 0:36:56Copyright (c) 2003, 2005, Oracle

10、. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release .0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsStarting SYSTEM.HS_HISJOB: system/*HS2021 schemas=hs_his dumpfile =expdp_test.dmp logfile=expdp_test.log

11、directory=TEST_EXPDP job_name=hs_hisjob Estimate in progress using BLOCKS method.Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 1.315 GB在執(zhí)行過程中，可以按Ctrl+C組合鍵退出當(dāng)前交互方式，退出之后，導(dǎo)出操作不會停頓，這不同于Oracle以前的EXP。以前的EXP，假設(shè)退出交互式方式，就會出錯終止導(dǎo)出義務(wù)。由于EXPDP是數(shù)據(jù)庫內(nèi)部定義的義務(wù)，曾經(jīng)與客戶端無關(guān)，退出交互之

12、后會進入export的命令行方式，此時支持status等查看命令：Export status -查看當(dāng)前JOB的形狀及相關(guān)信息DATA PUMP與傳統(tǒng)的EXP/IMP相比它還可以對正在運轉(zhuǎn)的JOB進展停頓和啟動：Export stop_job -暫停JOB此時經(jīng)過status命令查看到這時JOB的形狀值是UNDEFINEDExport start_job -重啟暫停的JOBExport kill_job -取消當(dāng)前的JOB并釋放相關(guān)客戶會話Export continue_client -經(jīng)過此命令查看已銜接JOB的日志Export exit_client -經(jīng)過此命令退出export方式-已退

13、出export方式后再次銜接去查看JOB的形狀用以下命令：oracleLinuxRedHat expdpdump$ expdp system/mingyue attach;oracleLinuxRedHat expdpdump$ expdp system/mingyue attach=system. HS_HISJOB;導(dǎo)出方式羅列：按表方式導(dǎo)出：expdp system/mingyueHS2021 tables=hs_his.hisholdsinfo,hs_his.hisfundjour dumpfile =expdp_test.dmp logfile=expdp_test.log dire

14、ctory=TEST_EXPDP job_name=hs_hisjob1;按查詢條件導(dǎo)出：expdp system/mingyueHS2021 tables=hs_his.hisfuassettot dumpfile =expdp_test.dmp logfile=expdp_test.log directory=TEST_EXPDP job_name=hs_hisjob2 query=where init_date between 20210501 and 20210701;留意：假設(shè)QUERY條件寫得有問題那么下面總是會報以下的錯誤ORA-39001: invalid argument v

15、alueORA-39035: Data filter SUBQUERY has already been specified.按表空間導(dǎo)出：Expdp system/mingyueHS2021 dumpfile=tablespace_test.dmp tablespaces=HS_HIS_DATA,HS_HIS_IDX logfile=tablespace_test.log directory=TEST_EXPDP job_name=hs_hisjob6;導(dǎo)出整個數(shù)據(jù)庫：expdp system/mingyueHS2021 dumpfile =full.dmp full=y logfile=f

16、ull.log directory=TEST_EXPDP job_name=hs_hisjob6;運用exclude,include導(dǎo)出數(shù)據(jù) Include導(dǎo)出用戶中指定類型的指定對象僅導(dǎo)出hs_his用戶下以HISFU開頭的一切表包含與表相關(guān)的索引，備注等不包含過程等其它對象類型：expdp hs_his/handsomeHS2021 dumpfile=include_1.dmp logfile=include_1.log directory=TEST_EXPDP job_name=job_hisjob7 include=TABLE:LIKE HISFU%;導(dǎo)出hs_his用戶下排除HISF

17、U開頭的一切表：expdp system/mingyueHS2021 schemas=hs_his dumpfile=include_1.dmp logfile=include_1.log directory=TEST_EXPDP job_name=job_hisjob7 include=TABLE:NOT LIKE HISFU%;僅導(dǎo)出hs_his用戶下的一切存儲過程：expdp system/mingyueHS2021 schemas=hs_his dumpfile=include_1.dmp logfile=include_1.log directory=TEST_EXPDP job_n

18、ame=job_hisjob7 include=PROCEDURE; Exclude導(dǎo)出用戶中指定類型的指定對象導(dǎo)出hs_his用戶下除出TABLE類型以外的一切對象，假設(shè)表不導(dǎo)出那么與表相關(guān)的索引，約束等與表有關(guān)聯(lián)的對象類型也不會被導(dǎo)出:expdp system/mingyueHS2021 schemas=hs_his dumpfile=exclude_1.dmp logfile=exclude_1.log directory=TEST_EXPDP job_name=job_hisjob7 exclude=TABLE;導(dǎo)出hs_his用戶下排除HISFU開頭的一切表：expdp hs_his

19、/handsomeHS2021 dumpfile=include_1.dmp logfile=include_1.log directory=TEST_EXPDP job_name=job_hisjob7 exclude=TABLE:LIKEHISFU%;導(dǎo)出hs_his用戶下的一切對象，但是對于表類型只導(dǎo)出以HISFU開頭的表：expdp hs_his/handsomeHS2021 dumpfile=include_1.dmp logfile=include_1.log directory=TEST_EXPDP job_name=job_hisjob7 exclude=TABLE:NOT L

20、IKE HISFU%;留意：假設(shè)content=data_only那么導(dǎo)出時就不能運用exclude,includeLINUX及UNIX對于特殊字符都要加一個本義字符如 ( )等這些字符在EXPDP中都要加上一個進展本義,否那么會有如下錯誤出現(xiàn)：oracleLinuxRedHat expdpdump$ expdp hs_his/handsomeHS2021 dumpfile=include_1.dmp logfile=include_1.log directory=TEST_EXPDP job_name=job_hisjob7 include=table:LIKE HISFU%;Export:

21、 Release .0 - Production on 星期六, 13 11月, 2021 17:54:42Copyright (c) 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release .0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsORA-39001: invalid argument valueORA-39000: bad dump file specificationORA-31641: unable to create dump file /u03/expdpdump/include_1.dmpORA-27038: created file already existsAdditional information: 1