【原】oracle external table

作者：david_zhang@sh 【转载时请以超链接形式标明文章】
链接：http://www.cnblogs.com/david-zhang-index/archive/2012/11/13/2768861.html

创建oracle外部表

1.采用oracle_datapump方式创建，该方式是利用查询结果对文件进行填充，好处是可以转移数据到平面文件中

1 create table emp_tab1(
2 ename,job,sal,dname
3 )
4 organization external
5 (type oracle_datapump
6 default directory dpdir
7 location('tb1.exp')
8 )
9 as select ename,job,sal,dname from emp join dept on emp.deptno=dept.deptno;

验证结果

 1 SQL> select * from emp_tab1;
 2 
 3 ENAME      JOB              SAL DNAME
 4 ---------- --------- ---------- --------------
 5 CLARK      MANAGER         2450 ACCOUNTING
 6 KING       PRESIDENT       5000 ACCOUNTING
 7 MILLER     CLERK           1300 ACCOUNTING
 8 JONES      MANAGER         2975 RESEARCH
 9 FORD       ANALYST         3000 RESEARCH
10 ADAMS      CLERK           1100 RESEARCH
11 SMITH      CLERK            800 RESEARCH
12 SCOTT      ANALYST         3000 RESEARCH
13 WARD       SALESMAN        1250 SALES
14 TURNER     SALESMAN        1500 SALES
15 ALLEN      SALESMAN        1600 SALES
16 
17 ENAME      JOB              SAL DNAME
18 ---------- --------- ---------- --------------
19 JAMES      CLERK            950 SALES
20 BLAKE      MANAGER         2850 SALES
21 MARTIN     SALESMAN        1250 SALES
22 
23 14 rows selected.

2.采用oracle_loader方式创建，

未完待续

posted @ 2012-11-13 21:55  david_zhang@sh  阅读(638)  评论(0)  编辑  收藏  举报