ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【Oracle】外部表的使用 external table(一)type: oracle_load

【Oracle】外部表的使用 external table(一)type: oracle_load

原创 Linux操作系统 作者:果酱o 时间:2012-07-23 15:28:10 0 删除 编辑

从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.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

  • 登陆被授权的用户scott创建外部表
CONN scott/tiger
CREATE 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_LOADER
DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS
(
records delimited by newline
badfile bad_dir:'empxt.bad' --如果不需要产生报错信息 此处可写 NOBADFILE
logfile log_dir:'empxt.log' --如果不需要产生日志信息 此处可写 NOLOGFILE
fields 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; --删除目录

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13304800/viewspace-736403/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-09-14

  • 博文量
    14
  • 访问量
    28050