oracle数据库外接有哪些,Oracle外部表有哪些实现方式？每种实现方式如何实现？...

基于oracle_datapump的外部表实现过程：

一、创建外部表以及产生dmp文件

1、创建directory，需要有 create any directory权限：

CREATE DIRECTORY admin AS '/oracle/admin';

2、创建外部表：

SQL> CREATE TABLE emp_xt

ORGANIZATION EXTERNAL

(

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY admin

LOCATION ('emp_xt.dmp')

)

AS SELECT * FROM emp;

Table created.

3、验证表的正确性：

SQL> select * from emp minus select * from emp_xt;

no rows selected

二、使用已产生的pump文件创建外部表

1、把产生的dmp文件复制到其它主机上

2、创建directory，需要有 create any directory权限：

SQL> create directory ext_dir as '/oracle/ext_dir';

3、创建外部表：

SQL> CREATE TABLE emp_xt2

(

EMPNO NUMBER(4),

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2)

)

ORGANIZATION EXTERNAL

(

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY ext_dir

LOCATION ('emp_xt.dmp')

);

三、如果基表发生改变，如何更新数据源：

1、先删除原数据库的外部表以及创建外部表产生的dmp文件。

2、重新创建外部表。

3、把新产生的dmp文件复制到远程主机上即可。

四、Create an external table with three dump files and with a degree of parallelism of three.

1、创建三个dump文件的外部表：

SQL> CREATE TABLE emp_xt3

ORGANIZATION EXTERNAL

(

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY admin

LOCATION ('emp_xt1.dmp', 'emp_xt2.dmp', 'emp_xt3.dmp')

)

PARALLEL 3

AS SELECT * FROM emp;

2、Compare the data unload against emp. There should be no differences.

SQL> SELECT * FROM emp MINUS SELECT * FROM emp_xt3;

五、Create an external table containing some rows from table emp.

1、创建包含有部分数据的外部表，主要目的产生dmp文件：

SQL> CREATE TABLE emp_part_xt

ORGANIZATION EXTERNAL

(

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY admin

LOCATION ('emp_p1_xt.dmp')

)

AS SELECT * FROM emp WHERE empno < 7654;

2、Create another external table containing the rest of the rows from emp.

SQL> drop table emp_part_xt;

Table dropped.

SQL> CREATE TABLE emp_part_xt

ORGANIZATION EXTERNAL

(

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY admin

LOCATION ('emp_p2_xt.dmp')

)

AS SELECT * FROM emp WHERE empno >= 7654;

3、Create an external table that uses the two dump files created in Steps 1 and 2.

SQL> CREATE TABLE emp_part_all_xt

(

EMPNO NUMBER(4),

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2)

)

ORGANIZATION EXTERNAL

(

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY admin

LOCATION ('emp_p1_xt.dmp','emp_p2_xt.dmp')

);

4、比较两张表：

SQL> select * from emp minus select * from emp_part_all_xt;

no rows selected

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

基于oracle_loader的外部表实现过程一：

1、编辑一个数据源，位于/oracle/load_dir，名为info.dat：

56november, 15, 1980 baker mary alice

87december, 20, 1970 roper lisa marie

2、sys用户执行如下的SQL语句：

CREATE DIRECTORY ext_tab_dir AS '/oracle/load_dir';

GRANT READ ON DIRECTORY ext_tab_dir TO SCOTT;

3、创建一张而外的表(可选)：

CREATE TABLE test (emp_no CHAR(6), last_name CHAR(25), first_name CHAR(20), middle_initial CHAR(1));

4、创建一张外部表，注意访问方式，经过检验，数据源是以位置来区分各个列的值，逗号和空格都计算在内：

CREATE TABLE test_load (employee_number CHAR(5), employee_last_name CHAR(20),

employee_first_name CHAR(15),

employee_middle_name CHAR(15))

ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir

ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS

(employee_number CHAR(2),

employee_dob CHAR(20),

employee_last_name CHAR(18),

employee_first_name CHAR(11),

employee_middle_name CHAR(11)))

LOCATION ('info.dat'));

5、通过外部表往test表插入数据：

INSERT INTO test (emp_no, first_name, middle_initial, last_name)

(SELECT employee_number, employee_first_name,

substr(employee_middle_name, 1, 1),

employee_last_name

FROM test_load);

6、或者通过外部表直接查询：

select * from test_load;

=========================================================================================

基于oracle_loader的外部表实现过程二：

1、产生数据源，位于/oracle/load_dir，名为info2.dat：

select empno||','||ename||','||job||','||mgr||','||hiredate||','||sal||','||comm||','||deptno from emp;

2、由于spool出来的源文件末尾包含了很多空格，所以需要用vi来处理一下，并且不能有空行，在vi中的命令如下：

:%s/ //g

3、创建一张外部表，注意访问方式，使用逗号来分割列域：

CREATE TABLE test2_load (

EMPNO NUMBER(4),

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2))

ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir

ACCESS PARAMETERS (FIELDS TERMINATED BY ',')

LOCATION ('info2.dat'));

4、select *from test2_load;

oracle视频教程请关注:http://down.51cto.com/4202939/up