[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文件中.