从Oracle 9i开始,通过External
table,可以直接以表格的形式访问外部文件,而不需要事先通过sqlldr将文件load进数据库。Oracle 9i的external
table是通过sqlldr引擎实现的。到了Oracle 10g,有增加了data pump方式的external
table,该方式不但可以读取外部文件,甚至可以将数据从数据库卸载出来到外部文件中,实现数据迁移功能,通过视图可以查看系统中的external
table的信息。
DBA_EXTERNAL_TABLES
ALL_EXTERNAL_TABLES
USER_EXTERNAL_TABLES
DBA_EXTERNAL_LOCATIONS
外部表描述:
数据在数据库外部组织,是操作系统文件。操作系统文件在数据库中的标志是通过一个逻辑目录来映射的数据是只读的。(外部表相当于一个只读的虚拟表)不可以在上面有DML操作,不可以创建索引可以进行查询操作和表连接,以及并行操作
创建外部表所需directory,需要有DBA用户创建,并给应用授权
SQL> create or replace
directory data_dir as '/home/oracle/external_table';
SQL> create or replace directory log_dir as
'/home/oracle/external_table/ext_log';
SQL> create or
replace directory bad_dir as '/home/oracle/external_table/ext_bad';
SQL> grant read on
directory data_dir to scott;
SQL> grant write on
directory log_dir to scott;
SQL> grant write on
directory bad_dir to scott;
系统中需要导入数据库的数据
[oracle@dy1
external_table]$ vi
emp1.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@dy1 external_table]$ vi emp2.dat401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard
登陆被授权的用户scott创建外部表
CONN
scott/tigerCREATE TABLE emp1(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_LOADERDEFAULT
DIRECTORY data_dirACCESS
PARAMETERS(records delimited by newlinebadfile bad_dir:'empxt.bad' --如果不需要产生报错信息 此处可写
NOBADFILElogfile log_dir:'empxt.log' --如果不需要产生日志信息 此处可写 NOLOGFILEfields terminated by ',' --字段是以
逗号为分割截取的missing field values are null --缺少的字段视为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
('emp1.dat'))PARALLEL
REJECT LIMIT
UNLIMITED/
注意:文件emp1.dat,一定要是存放在之前创建的我们刚刚创建的oracle的directory目录下的。
最后一句“REJECT
LIMIT UNLIMITED”,是告诉Oracle
这个外部表没有行数限制。否则,当文件中的数据量超过200万行时,在对表进行检索时,就会出现ORA-30653
错误。
对于外部表的管理
ALTER TABLE
alert_fgisdb LIMIT 100; --更改拒绝限制
ALTER TABLE
alert_fgisdb DIRECTORY DEFAULT DIRECTORY bdump; --更改默认目录说明ALTER TABLE alert_fgisdb
PARAMETERS ACCESS PARAMETERS (FIELDS TERMINATED BY '|');
--修改访问参数,如分隔符由","变为"|"ALTER TABLE alert_fgisdb
LOCATION('TC_REG_MNGREGIONCODE.txt'); --修改文件位置:drop table alert_fgisdb; --删除外部表drop DIRECTORY bdump; --删除目录