外部表是表结构被存放在数据字典，而表数据被存放在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
----------- ---------- ---------- ---------- ---------- ------------------- ---------- -------------- ------------- ----------
查看本栏目更多精彩内容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/