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