編寫PL/SQL程式使用資料泵匯出匯入資料

標籤：oracle 資料匯入匯出

1.什麼是資料泵？就是oracle的一個PL/SQL程式包 dbms_datapump
2.資料泵技術是Oracle Database 10g 中的新技術，它比原來匯入/匯出(imp,exp)技術快15－45倍。速度的提高源於使用了並行技術來讀寫匯出轉儲檔案。

expdp使用

　　使用EXPDP工具時，其轉儲檔案只能被存放在DIRECTORY對象對應的OS目錄中，而不能直接指定轉儲檔案所在的OS目錄。因此使用EXPDP工具時，,必須首先建立DIRECTORY對象，並且需要為資料庫使用者授予使用DIRECTORY對象許可權。

首先得建DIRECTORY：

　　SQL> conn /as sysdba

　　SQL> CREATE OR REPLACE DIRECTORY mydir as ‘/home/oracle/backup‘

　　SQL> GRANT read,write ON DIRECTORY mydir TO public;
3.編寫PL/SQL程式使用資料泵
4.匯出scott使用者下的dept 和emp 表

set serveroutput ondeclare   --定義資料泵  h number;begin  --開啟資料泵  h := dbms_datapump.open(operation=>‘EXPORT‘,job_mode=>‘TABLE‘ );  --指定中繼資料 1.指定scott 2.指定表：dept和emp  dbms_datapump.metadata_filter(handle=>h,                                name=>‘SCHEMA_EXPR‘,                                value=>‘IN(‘‘SCOTT‘‘)‘);--必須大寫  dbms_datapump.metadata_filter(handle=>h,name=>‘NAME_EXPR‘,value=>‘IN (‘‘DEPT‘‘,‘‘EMP‘‘)‘);--必須大寫                              --指定匯出的檔案：dmp  dbms_datapump.add_file(handle=>h,                         filename=>‘demp.dmp‘,                         directory=>‘MYDIR‘);  --執行匯出任務  dbms_datapump.start_job(handle=>h);  --關閉資料本（任務）  dbms_datapump.detach(handle=>h);  dbms_output.put_line(‘finished‘);end;/

5.匯出scott使用者下所有的對象 oracle下一個使用者就是一個schema

set serveroutput ondeclare  h number;begin   --開啟資料庫  h := dbms_datapump.open(operation=>‘EXPORT‘,job_mode=>‘SCHEMA‘);  --指定中繼資料 1.指定scott   前面已經指定了匯出的整個scott的方案 不用再指定了 2.指定表：dept和emp  dbms_datapump.metadata_filter(handle=>h, name=>‘SCHEMA_EXPR‘,value=>‘IN(‘‘SCOTT‘‘)‘);--必須大寫    --指定匯出的檔案：dmp  dbms_datapump.add_file(handle=>h,filename=>‘demp1.dmp‘,directory=>‘MYDIR‘);  --執行匯出任務  dbms_datapump.start_job(handle=>h);  --關閉資料本（任務）  dbms_datapump.detach(handle=>h);  dbms_output.put_line(‘finished‘);end;/

6.scott的表匯入到scott1使用者下
使用dba 建立新使用者 grant connect,source to scott1 identified by password;

declare  --定義資料泵（任務）  h number;begin  --開啟一個任務  h := dbms_datapump.open(operation=>‘IMPORT‘,job_mode=>‘TABLE‘);  --指定到入檔案的位置  dbms_datapump.add_file(handle=>h,filename=>‘demp.dmp‘,directory=>‘MYDIR‘);  --到入指定的使用者 scott=>scott1  dbms_datapump.metadata_remap(handle=>h,name=>‘REMAP_SCHEMA‘,old_value=>‘SCOTT‘,value=>‘SCOTT1‘);   --執行匯出任務  dbms_datapump.start_job(handle=>h);  --關閉資料本（任務）  dbms_datapump.detach(handle=>h);   dbms_output.put_line(‘匯出成功‘);end;/

7.scott的schema匯入到scott1使用者下
使用dba 建立新使用者 grant connect,source to scott2 identified by password;

set serveroutput ondeclare  h number;begin   --開啟資料庫  h := dbms_datapump.open(operation=>‘EXPORT‘,job_mode=>‘SCHEMA‘);  --指定中繼資料 1.指定scott   前面已經指定了匯出的整個scott的方案 不用再指定了 2.指定表：dept和emp  dbms_datapump.metadata_filter(handle=>h, name=>‘SCHEMA_EXPR‘,value=>‘IN(‘‘SCOTT‘‘)‘);--必須大寫    --指定匯出的檔案：dmp  dbms_datapump.add_file(handle=>h,filename=>‘demp1.dmp‘,directory=>‘MYDIR‘);  --執行匯出任務  dbms_datapump.start_job(handle=>h);  --關閉資料本（任務）  dbms_datapump.detach(handle=>h);  dbms_output.put_line(‘finished‘);end;/

注意：次操作過程全程都是在一個資料庫下進行操作，相當於移動資料

編寫PL/SQL程式使用資料泵匯出匯入資料