[20140918]資料泵與外部檔案.txt

[20140918]資料泵與外部檔案.txt

--昨天幫別人匯入一些資料,看檔名稱以為是使用exp/expdp匯出的,測試不對!還好壓縮包裡面有一個文件,才知道使用資料泵的外部檔案格式.
--自己晚上回去看了一些文件,做了一些測試:

SCOTT@test> @ver

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create or replace directory data_pump_dir as '/u01/app/oracle11g/admin/test/dpdump/';
grant read, write on directory data_pump_dir to exp_full_database;
grant read, write on directory data_pump_dir to imp_full_database;
grant read, write on directory data_pump_dir to scott;

create table emp_unload organization external
(type oracle_datapump
default directory data_pump_dir
location ('emp_unload.dat')
)
as select * from emp ;

--注意型別oracle_datapump,這樣就建立了外部檔案emp_unload.dat.

SCOTT@test> host ls -l  /u01/app/oracle11g/admin/test/dpdump/emp*
-rw-r-----  1 oracle11g oinstall 12288 Sep 18 17:10 /u01/app/oracle11g/admin/test/dpdump/emp_unload.dat

SCOTT@test> select * from emp_unload ;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

14 rows selected.

--另外以後還可以使用這個定義修改檔案,載入新的資料.

--繼續測試:
create table emp_unload1 organization external
(type oracle_datapump
default directory data_pump_dir
location ('emp_unload1.dat')
)
as select * from emp where deptno=10;

SCOTT@test> alter table emp_unload location ('emp_unload1.dat');
Table altered.

SCOTT@test> select * from emp_unload ;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

--僅僅包含deptno=10的資料在emp_unload1.dat檔案中.