oracle外部表逗号分隔符,【Oracle】外部表的使用 external table（一）type: oracle_load...

从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; --删除目录