涉及到对外部表的操作,仅能使用select操作,insert,update,deleter操作是不允许的
1、先创建目录对象及位置
SQL> sho user;
USER is "SYS"SQL> create directory ext_data_dir as '/home/oracle/app/oracle/oradata/ext_data';
Directory created.
SQL> grant read,write on directory ext_data_dir to hr;
Grant succeeded.SQL>
如果没有创建这个目录对象,则在进行创建外部表的时候会报:ORA-06564: object EXT_DATA_DIR does not exist
2、创建外部表(从database现有表中选取,实际上是将普通表卸载数据到外部表)
create table employees_ext
organization external(
type oracle_datapump
default directory ext_data_dir
location ('employees.dmp')
)
as select * from new_employees3;
创建完后在/home/oracle/app/oracle/oradata/ext_data这个目录下会有
~/app/oracle/oradata/ext_data> ls
employees.dmp EMPLOYEES_EXT_117273.log EMPLOYEES_EXT_25181.log
其中两个log文件分别记录外部表被打开的时间:
> cat EMPLOYEES_EXT_25181.log
LOG file opened at 04/01/13 16:55:37
3、对外部表进行操作(从database现有表中选取)
a、从外部表装载数据到普通表
先创建一个普通表:
create table new_employees4_yearly_payment
(employee_id number(6,0),
yearly_salary number,
department_id number(4,0),
last_name varchar2(25 BYTE),
first_name VARCHAR2(20 BYTE)
);
然后再从外部表中装载数据到该表:
insert into new_employees4_yearly_payment
(employee_id, yearly_salary, department_id, last_name, first_name)
select
employee_id, salary_new*12, department_id, last_name, first_namefrom employees_ext
where department_id=66
commit;
FIRST_NAME LAST_NAME YEARLY_SALARY
-------------------- ------------------------- -------------
weiyi kong 96000
yifei liu 1200
binbin fan 1200
SQL>