Oracle数据库外部表实验过程
2018-01-16 15:55:25
详情: 使用datadump提供外部表的定义并创建外部表1
a.创建系统目录以及Oracle数据目录名来建立对应关系,同时授予权限 SQL> create or replace directory dat_dir as '/home/oracle/external_tb/data/'; SQL> grant read,write on directory dat_dir to scott; SQL> conn scott/scott Connected. SQL> create table ex_tb1 (ename,job,sal,dname) organization external ( type oracle_datapump default directory dat_dir location('tb1.exp,tb2.exp')) parallel 2 as select ename,job,sal,dname from emp join dept on emp.deptno=dept.deptno ; Table created. SQL> select * from ex_tb1; ENAME JOB SAL DNAME ---------- --------- ---------- -------------- SMITH CLERK 800 RESEARCH ALLEN SALESMAN 1600 SALES WARD SALESMAN 1250 SALES JONES MANAGER 2975 RESEARCH MARTIN SALESMAN 1250 SALES BLAKE MANAGER 2850 SALES CLARK MANAGER 2450 ACCOUNTING SCOTT ANALYST 3000 RESEARCH KING PRESIDENT 5000 ACCOUNTING TURNER SALESMAN 1500 SALES ADAMS CLERK 1100 RESEARCH ENAME JOB SAL DNAME ---------- --------- ---------- -------------- JAMES CLERK 950 SALES FORD ANALYST 3000 RESEARCH MILLER CLERK 1300 ACCOUNTING 14 rows . a.平面文件数据 $ pwd 创建外部表 create table emp_new SQL> select * from emp_new; SQL> delete from emp_new; SQL>select owner,table_name,type_name,default_directory_name,access_parameters 获得平面文件的位置,使用如下的查询: SQL>select * from dba_external_locations order by table_name;
[oracle@oradb ~]$ mkdir –p /home/oracle/external_tb/data
SQL> alter user scott account unlock identified by scott;
b.创建外部表
c.验证外部表
SQL> select * from ex_tb1;
ENAME JOB SAL DNAME
---------- --------- ---------- --------------
SMITH CLERK 800 RESEARCH
ALLEN SALESMAN 1600 SALES
..................................
MILLER CLERK 1300 ACCOUNTING
对于使用上述方式创建的外部表可以将其复制到其他路径作为外部表的原始数据来生成新的外部表,用于转移数据。2
1.dat:
7369,SMITH,CLERK,7902,17-DEC-80,100,0,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,250,0,30
7521,WARD,SALESMAN,7698,22-FEB-81,450,0,30
7566,JONES,MANAGER,7839,02-APR-81,1150,0,20
2.dat:
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,0,30
7698,BLAKE,MANAGER,7839,01-MAY-81,1550,0,30
7934,MILLER,CLERK,7782,23-JAN-82,3500,0,10
/home/oracle/external_tb/data
$ ls
1.dat 2.dat dat_dir:tb_test.exp EMP_NEW_3198.log EMP_NEW_3413.log EX_TB1_3021.log
(
emp_id number(4),
ename varchar2(15),
job varchar2(12) ,
mgr_id number(4) ,
hiredate date,
salary number(8),
comm number(8),
dept_id number(2)
)
organization external
(
type oracle_loader
default directory dat_dir
access parameters
(
records delimited by newline
fields terminated by ','
)
location
('1.dat','2.dat')
);
验证外部表
EMP_ID ENAME JOB MGR_ID HIREDATE SALARY COMM DEPT_ID
---------- --------------- ------------ ---------- --------- ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 0 30
....................................................................
外部表不能执行DML
delete from emp_new
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
查看外部表信息
2 from dba_external_tables;
网上报名 免费试学+报名即可领取免费资料