管理表（六）--外部表的管理

外部表是表结构被存放在数据字典，而表数据被存放在OS文件的表。通过使用外部表，不仅可以在数据库中查询OS文件的数据，还可以使用INSERT方式将OS文件数据装载到数据库中，从而实现SQL*Loader所提供的功能。建立外部表后，可以查询外部表的数据，在外部表上执行连接查询，或对外部表的数据进行排序。需要注意，外部表上不能执行DML修改，也不能在外部表上建立索引。

1、建立外部表

——准备工作：

[oracle@solaris10 ~]$mkdir /export/home/oracle/dat

[oracle@solaris10 ~]$cd /export/home/oracle/dat

[oracle@solaris10 dat]$vi empxt1.dat

360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus

361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper

362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr

363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda

[oracle@solaris10 dat]$vi empxt2.dat

401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel

402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega

403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins

404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard

——建立对应的目录：

SQL> conn /as sysdba

Connected.

SQL> create or replace directory admin_dat_dir

2 as '/export/home/oracle/dat';

Directory created.

SQL> create or replace directory admin_log_dir

2 as '/export/home/oracle/log';

Directory created.

SQL> create or replace directory admin_bad_dir

2 as '/export/home/oracle/bad';

Directory created.

SQL> !

[oracle@solaris10 ~]$mkdir /export/home/oracle/{log,bad}

[oracle@solaris10 ~]$ls

1 Documents core dat local.login shell

1.sql afiedt.buf cr_anny_db.sql hell.txt local.profile x86

Desktop bad cr_dict.sql local.cshrc log

——授权scott 可以访问所建立的目录

SQL> grant read on directory admin_dat_dir to scott;

Grant succeeded.

SQL> grant write on directory admin_log_dir to scott;

Grant succeeded.

SQL> grant write on directory admin_bad_dir to scott;

Grant succeeded.

——建立外部表

SQL> conn scott/tiger

Connected.

SQL>

CREATE TABLE admin_ext_employees

(employee_id NUMBER(4),

first_name VARCHAR2(20),

last_name VARCHAR2(25),

job_id VARCHAR2(10),

manager_id NUMBER(4),

hire_date DATE,

salary NUMBER(8,2),

commission_pct NUMBER(2,2),

department_id NUMBER(4),

email VARCHAR2(25)

)

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY admin_dat_dir

ACCESS PARAMETERS

(

records delimited by newline

badfile admin_bad_dir:'empxt%a_%p.bad'

logfile admin_log_dir:'empxt%a_%p.log'

fields terminated by ','

missing field values are null

( employee_id, first_name, last_name, job_id, manager_id,

hire_date char date_format date mask "dd-mon-yyyy",

salary, commission_pct, department_id, email

)

LOCATION ('empxt1.dat', 'empxt2.dat')

)

PARALLEL

REJECT LIMIT UNLIMITED;

Table created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

TEST TABLE

ADMIN_EXT_EMPLOYEES TABLE

6 rows selected.

——查询外部表记录

SQL> select * from ADMIN_EXT_EMPLOYEES;

EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID MANAGER_ID HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_ID EMAIL

----------- ---------- ---------- ---------- ---------- ------------------- ---------- -------------- ------------- ----------

360 Jane Janus ST_CLERK 121 2001-05-17 00:00:00 3000 0 50 jjanus

361 Mark Jasper SA_REP 145 2001-05-17 00:00:00 8000 .1 80 mjasper

362 Brenda Starr AD_ASST 200 2001-05-17 00:00:00 5500 0 10 bstarr

363 Alex Alda AC_MGR 145 2001-05-17 00:00:00 9000 .15 80 aalda

401 Jesse Cromwell HR_REP 203 2001-05-17 00:00:00 7000 0 40 jcromwel

402 Abby Applegate IT_PROG 103 2001-05-17 00:00:00 9000 .2 60 aapplega

403 Carol Cousins AD_VP 100 2001-05-17 00:00:00 27000 .3 90 ccousins

404 John Richardson AC_ACCOUNT 205 2001-05-17 00:00:00 5000 0 110 jrichard

10 rows selected.

只能读，不能做dml

SQL> delete from ADMIN_EXT_EMPLOYEES;

delete from ADMIN_EXT_EMPLOYEES

ERROR at line 1:

ORA-30657: operation not supported on external organized table

——把外部表数据插入到oracle 表里

SQL> create table employees as select * from admin_ext_employees where 1=2;

Table created.

SQL> insert into employees select * from admin_ext_employees;

10 rows created.

SQL> select * from employees;