Oracle使用資料泵卸載資料

Oracle 10G提供了一種方式可以使用外部表格來抽取資料。

首先需要一個directory目錄（這裡繼續使用前面建立的目錄）

1. SQL> show user  
2. USER 為 "SYS"  
3. SQL> select * from dba_directories where DIRECTORY_NAME like '%SYS%';  
4.   
5. OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH  
6. ------------------------------ ------------------------------ --------------------------------------------------   
7. SYS                            SYS_SQLLDR_XT_TMPDIR_00000     /u01/sqlldr  

然後執行命令

1. SQL> show user  
2. USER 為 "ING"  
3. SQL> desc SYS_SQLLDR_X_EXT_DEPT  
4.  名稱                                      是否為空白? 類型  
5.  ----------------------------------------- -------- ----------------------------   
6.  DEPTNO                                             NUMBER(10)  
7.  DNAME                                              VARCHAR2(20)  
8.  LOC                                                VARCHAR2(20)  
9.   
10. SQL> create table SYS_SQLLDR_X_EXT_DEPT_UNLOAD  
11.   2  organization external  
12.   3  (type oracle_datapump  
13.   4   default directory SYS_SQLLDR_XT_TMPDIR_00000  
14.   5   location('SYS_SQLLDR_X_EXT_DEPT_UNLOAD.dat')  
15.   6  ) as  
16.   7  select * from SYS_SQLLDR_X_EXT_DEPT;  
17.   
18. 表已建立。  

在資料庫中會建立一個SYS_SQLLDR_X_EXT_DEPT_UNLOAD表，在目錄中會多出兩個檔案

1. [oracle@linux sqlldr]$ ll | grep SYS  
2. -rw-r--r-- 1 oracle oinstall   148 10-04 22:43 SYS_SQLLDR_X_EXT_DEPT_UNLOAD_3501.log   
3. -rw-r----- 1 oracle oinstall 12288 10-04 22:40 SYS_SQLLDR_X_EXT_DEPT_UNLOAD.dat  

最後把SYS_SQLLDR_X_EXT_DEPT_UNLOAD.dat檔案複製到其它資料庫對應目錄中，提取外部表格建立指令碼就可以拉

1. SQL> set long 20000  
2. SQL> set pagesize 99  
3. SQL> select dbms_metadata.get_ddl('TABLE','SYS_SQLLDR_X_EXT_DEPT_UNLOAD') from dual;  
4.   
5. DBMS_METADATA.GET_DDL('TABLE','SYS_SQLLDR_X_EXT_DEPT_UNLOAD')  
6. --------------------------------------------------------------------------------   
7.   
8.   CREATE TABLE "ING"."SYS_SQLLDR_X_EXT_DEPT_UNLOAD"  
9.    (    "DEPTNO" NUMBER(10,0),  
10.         "DNAME" VARCHAR2(20),  
11.         "LOC" VARCHAR2(20)  
12.    )  
13.    ORGANIZATION EXTERNAL  
14.     ( TYPE ORACLE_DATAPUMP  
15.       DEFAULT DIRECTORY "SYS_SQLLDR_XT_TMPDIR_00000"  
16.   
17.       LOCATION  
18.        ( 'SYS_SQLLDR_X_EXT_DEPT_UNLOAD.dat'  
19.        )  
20.     )  

甚至可以從外包表中直接載入資料

1. SQL> insert /*+ append */ into dept select * from SYS_SQLLDR_X_EXT_DEPT_UNLOAD;  
2.   
3. 已建立4行。  
4.   
5. SQL> commit;  
6.   
7. 提交完成。  
8.   
9. SQL> select * from dept;  
10.   
11.     DEPTNO DNAME                LOC  
12. ---------- -------------------- --------------------   
13.         50 Sales                Virginia  
14.         60 Accounting           Virginia  
15.         70 Consulting           Virginia  
16.         80 Finance              Virginia