oracle 建外部表步骤

创建Oracle外部表 External Table（转载）

博客分类：
• Oracle

 

建立外部表的步骤：

     1、创建以“,”分隔的文件“TestTable.csv”至“D:\Test”

     2、创建一个Directory：

     create directory TestTable_dir as 'D:\Test' ;

     3、创建一个外部表：

    create table TestTable(

       ID varchar2 ( 10 ),

       NAME varchar2 ( 20 ),

       TYPE varchar2 ( 20 ),

       AGEvarchar2 ( 20 ))

    organization external (

       type oracle_loader

       default directory TestTable_dir

       access parameters (fields terminatedby ',' )

       location ( 'TestTable.csv' )

       );

各类参数说明

     1、type oracle_loader

      数据转换驱动器，oracle_loader为默认，也可以改换其他

     2、defaultdirectory TestTable_dir

        location ('TestTable.csv')

      指定外部表所在文件夹以及指定文件

     3、accessparameters

     设置转换参数，例如(fields terminatedby',')表示以','为字段间的分隔符

       ● 参数由访问驱动程序定义

外部表的错误处理

     1、REJECT LIMIT子句

      在创建外部表时最后加入LIMIT子句，表示可以允许错误的发生个数。

       * 默认的REJECT LIMIT值为0

       * REJECT LIMIT UNLIMITED则不会报错

     2、BADFILE 和 NOBADFILE 子句

      在accessparameters中加入BADFILE'BAD_FILE.txt'子句，则所有数据转换错误的值会被放入'BAD_FILE.txt'中

      使用NOBADFILE子句则表示忽略转换错误的数据

       ● 如果不写BADFILE或NOBADFILE，则系统自动在源目录下生成与外部表同名的.BAD文件

       ● BADFILE只能记录前1次操作的结果，他会被第2次操作所覆盖。

     3、LOGFILE 和 NOLOGFILE 子句

      在accessparameters中加入LOGFILE'LOG_FILE.log'子句，则所有Oracle的错误信息放入'LOG_FILE.log'中

      使用NOLOGFILE子句则表示不记录错误信息到log中

       ● 如果不写LOGFILE或NOLOGFILE，则系统自动在源目录下生成与外部表同名的.LOG文件

修改外部表语句

    外部表与堆表一样可以之用ALTER TABLE命令修改表属性

     * REJECT LIMIT         --错误数

     * DEFAULT DIRECTORY    --默认目录

     * ACCESS PARAMETERS    --参数

     * LOCATION             --数据文件

     * ADD COLUMN           --增加列

     * MODIFY COLUMN        --列定义

     * DROP COLUMN          --删除列

     * RENAME TO            --外部表更名

其他约束

     ● 外部表无法使用insert、update、delete等操作，要修改其数据只能通过修改数据文件。

     ● 外部表不能建立索引，如要建立，则需要先create table XX as select * from TestTable

PS:




     1.外部表可以加载和卸载数据泵格式的数据，只需把organization external里的参数type设置为oracle_datapump。




            create table all_objects_unload

             organization external

                   (

                   type oracle_datapump

                  default directory testdir

                  location('allobjects.dat')

                  )

             as

            select * from all_objects






















转载文章2：




原文地址：http://blog.sina.com.cn/s/blog_53aed4430100cu45.html







有时候，数据库海量数据的存储，是一个令人头疼的问题。别的不说，光是频繁的执行insert（1000次/秒）都是一场恶梦。但是，如果将数据保存到文家里，而不是数据库中，数据序列化的开销就小得多了。但是，对于文件的各种复杂检索，又是一件相当麻烦的事。




    幸好，Oracle有这样一种特性，它可以将某些特定格式的文件映射到数据库中，形成一个“表”，称为“外部表”。单用户更改文件内容时，外部表中的数据即随之改变。同时，用户又可以像检索普通表一样，以只读的方式对外部表进行检索。




    我们假设有这样一个文件（DATA.TXT）：

1|this is a string

2|这里是个字符串

3|ABC

    要把这样一个文件映射成外部表，有以下工作要做：

    首先，我们需要为Oracle创建一个Directory，

    创建方式为，在数据库中执行，须用DBA用户创建，并给应用授权。

    create directory EXT_TABLE_DIR as '/home/oracle/app/oracle/oradata/php/'

    注意“/home/oracle/app/oracle/oradata/php/”是一个存在于Oracle数据库服务器本身上边的实际存在的文件夹；

    然后，将DATA.TXT文件拷贝到上述文件夹下；

    最后，创建一个对应外部表，

create table EXT_TABLE_NAME

(

COL_1 NUMBER,

COL_2 VARCHAR2(512)

)

organization external

(

type oracle_loader

default directory EXT_TABLE_DIR

access parameters ( fields terminated by '|' )

location ('DATA.TXT')

)

reject limit unlimited

    注意蓝色部分，EXT_TABLE_NAME是要映射成的外部表名称，EXT_TABLE_DIR是第一步里我们创建的Oracle的Directory，“|”是文件里的分割符，DATA.TXT是文件名。




    需要补充的是，最后有一句“reject limit unlimited”，告诉Oracle这个外部表没有行数限制。否则，当文件中的数据量超过200万行时，在对表进行检索时，就会出现ORA-30653，“reject limit reached”错误。



Oracle 9i 的一项新特性就是 External Table，它就象通常的数据库表一样，拥有字段和数据类型约束，并且可以查询，但是表中的数据却不存储在数据库中，而是在与数据库相关联的普通外部文件里。当你查询 External Table 时，Oracle 将解析该文件并返回符合条件的数据，就象该数据存储在数据库表中一样。

具体的定义可以参见《 Oracle 概念手册》，以下的几点需要注意：

<一>:外部表的描述:

> 创建的语法类似于： "CREATE TABLE ... ORGANIZATION EXTERNAL"

> 数据在数据库的外部组织，是操作系统文件。

> 操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。

> 数据是只读的。（外部表相当于一个只读的虚表）

> 不可以在上面运行任何 DML 操作，不可以创建索引。

> 可以查询操作和连接。可以并行操作。

例子:

假如有如下两个数据文件:

1: 数据文件的格式

F1.TXT文件:

13234,FIRSTS

46464,TESTA

F2.TEXT文件:

13234,SECONDS

46464,TEST

2:创建目录,并用DBA进行授权;

sql> create directory test_dir as 'E:temp';

sql>grant read,write on directory test_dir to users;

注意：创建完毕逻辑目录之后要把平面文件拷贝到该目录下，另外还要注意文件名字不要写错。

      一定要给oracle用户对这个目录可读可写的权限，操作系统层面，如使用chmod -R 777 test_dir；

3:使用被授权的用户users创建外部表:

create table test_table

(ms_no varchar(20),

tip varchar(20),

descs varchar(20))

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY test_dir

ACCESS Parameters

(

RECORDS DELIMITED BY NEWLINE

badfile 'bad_dev.txt'

LOGFILE 'log_dev.txt'

FIELDS TERMINATED BY ','

MISSING FIELD VALUES ARE NULL

(ms_no,tip,descs)

)

LOCATION('F1.txt','F2.txt')

)

;

表创建完成.当然也可以导入一个文件




4:进行SELECT 操作看是否正确;

SQL>select * from test_table

结果如下:

MS_NO TIP DESCS

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

13234 FIRSTS

46464 TESTA

13234 SECONDS

46464 TEST



<二>: 如何得到外部表的有关信息:

SQL> DESC DBA_EXTERNAL_TABLES;

Name Type Nullable

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

OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

TYPE_OWNER CHAR(3) Y

TYPE_NAME VARCHAR2(30)

DEFAULT_DIRECTORY_OWNER CHAR(3) Y

DEFAULT_DIRECTORY_NAME VARCHAR2(30)

REJECT_LIMIT VARCHAR2(40) Y

ACCESS_TYPE VARCHAR2(7) Y

ACCESS_PARAMETERS VARCHAR2(4000) Y

SQL>SELECT OWNER,TABLE_NAME,DEFAULT_DIRECTORY_NAME,ACCESS_PARAMETERSFRFROM

DBA_EXTERNAL_TABLES;

可以得到外部表的相关信息;




<三>:如何得到外部路径的信息:

SQL> desc DBA_EXTERNAL_LOCATIONS;

得到该表结构:

Name Type Nullable

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

OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

LOCATION VARCHAR2(4000) Y

DIRECTORY_OWNER CHAR(3) Y

DIRECTORY_NAME VARCHAR2(30) Y

SQL> select * from DBA_EXTERNAL_LOCATIONS;

得到具体信息;







转载文章3




原文地址：http://apps.hi.baidu.com/share/detail/17037186




ORACLE外部表的应用实例

Oracle 9i 的一项新特性就是 External Table，它就象通常的数据库表一样，拥有字段和数据类型约束，并且可以查询，但是表中的数据却不存储在数据库中，而是在与数据库相关联的普通外部文件里。当你查询 External Table 时，Oracle 将解析该文件并返回符合条件的数据，就象该数据存储在数据库表中一样。

具体的定义可以参见《 Oracle 概念手册》，以下的几点需要注意：




<一>:外部表的描述:




> 创建的语法类似于： "CREATE TABLE ... ORGANIZATION EXTERNAL"

> 数据在数据库的外部组织，是操作系统文件。

> 操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。

> 数据是只读的。（外部表相当于一个只读的虚表）

> 不可以在上面运行任何 DML 操作，不可以创建索引。

> 可以查询操作和连接。可以并行操作。




例子:

假如有如下两个数据文件:

1: 数据文件的格式

bjuser.csv文件:




20080629,修改,1301110022,邹雪辉,01110022

20080629,修改,1302050023,王晓斌,02050023

20080629,修改,1306060130,邵静,06060130

20080629,修改,1304020386,张晋,04020386

20080629,修改,1301070082,许征,01070082




2:创建目录,并进行授权;

sql> create or replace directory out_tabdir as '/oradata';

sql>grant read,write on directory out_tabdir to users;

注意：创建完毕逻辑目录之后要把平面文件拷贝到该目录下，另外还要注意文件名字不要写错。

3:创建外部表:




Create table bjuser

(yyyymm varchar2(8),

pro_no varchar2(50),

user_id varchar2(20),

user_nm varchar2(20),

user_no varchar2(20)

)

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY out_tabdir




ACCESS Parameters

(

RECORDS DELIMITED BY NEWLINE

badfile 'bad_bjuser.txt'    /* 这些文件是临时生成的文件,命名随便*/

LOGFILE 'log_bjuser.txt'

FIELDS TERMINATED BY ','

MISSING FIELD VALUES ARE NULL

(yyyymm,pro_no,user_id,user_nm,user_no)

)

LOCATION('bjuser.csv')

)reject limit unlimited




表创建完成.当然也可以导入一个文件




4:进行SELECT 操作看是否正确;

SQL>select * from bjuser




结果如下:

SQL> select * from bjuser;




YYYYMM   PRO_NO                                             USER_ID              USER_NM              USER_NO

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

20080629 修改                                               1301110022           邹雪辉               01110022

20080629 修改                                               1302050023           王晓斌               02050023

20080629 修改                                               1306060130           邵静                 06060130

20080629 修改                                               1304020386           张晋                 04020386

20080629 修改                                               1301070082           许征                 01070082

20080629 修改 




<二>: 如何得到外部表的有关信息:

SQL> DESC DBA_EXTERNAL_TABLES;

Name Type Nullable

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

OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

TYPE_OWNER CHAR(3) Y

TYPE_NAME VARCHAR2(30)

DEFAULT_DIRECTORY_OWNER CHAR(3) Y

DEFAULT_DIRECTORY_NAME VARCHAR2(30)

REJECT_LIMIT VARCHAR2(40) Y

ACCESS_TYPE VARCHAR2(7) Y

ACCESS_PARAMETERS VARCHAR2(4000) Y




SQL>SELECT OWNER,TABLE_NAME,DEFAULT_DIRECTORY_NAME,ACCESS_PARAMETERSFRFROM

DBA_EXTERNAL_TABLES;

可以得到外部表的相关信息;







<三>:如何得到外部路径的信息:

SQL> desc DBA_EXTERNAL_LOCATIONS;

得到该表结构:

Name Type Nullable

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

OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

LOCATION VARCHAR2(4000) Y

DIRECTORY_OWNER CHAR(3) Y

DIRECTORY_NAME VARCHAR2(30) Y




SQL> select * from DBA_EXTERNAL_LOCATIONS;

<四> 卸载外部表




drop table bjuser




<五> 修改外部表




更改拒绝限制

ALTER TABLE 外部表 LIMIT 100;

更改默认目录说明

ALTER TABLE 外部表 DIRECTORY DEFAULT DIRECTORY 新目录路径;

修改访问参数,如分隔符由","变为"|"

ALTER TABLE 外部表 PARAMETERS ACCESS PARAMETERS (FIELDS TERMINATED BY '|');

修改文件位置:

ALTER TABLE 外部表   LOCATION('TC_REG_MNGREGIONCODE.txt');




<六>sqlldr生成外部表语句

在Oracle 9i中，sqlldr增加了一个新的参数external_table。通过这个参数的generate_only选项，可以生成完整的外部表创建语句。




现在有一个sqlldr控制文件，内容如下：




load data

infile 'd:\sqldr\test.txt'

badfile 'd:\sqldr\test.bad'

discardfile 'd:\sqldr\test.dis'

append into table test

fields terminated by X'09'

trailing nullcols

(

id,

name

)




利用如下命令行生成完整的外部表创建语句：




C:\>sqlldr test/test@acf control=d:\sqldr\test.ctl external_table=generate_only




在c：\根目录下找到test.log文件，这个文件包括了非常详细的内容，找到相应部分




用于外部表的 CREATE TABLE 语句:

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

CREATE TABLE "SYS_SQLLDR_X_EXT_TEST"

(

"ID" NUMBER(38),

"NAME" VARCHAR2(10)

)

ORGANIZATION external

(

TYPE oracle_loader

DEFAULT DIRECTORY TEST_DIR

ACCESS PARAMETERS

(

    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK

    BADFILE 'TEST_DIR':'test.bad'

    DISCARDFILE 'TEST_DIR':'test.dis'

    LOGFILE 'test.log_xt'                                    /*注意：这几个文件的路径不能有绝对路径，否则会抱错*/

    READSIZE 1048576

    FIELDS TERMINATED BY 0x'09' LDRTRIM

    MISSING FIELD VALUES ARE NULL

    REJECT ROWS WITH ALL NULL FIELDS

    (

      "ID" CHAR(255)

        TERMINATED BY 0x'09',

      "NAME" CHAR(255)

        TERMINATED BY 0x'09'

    )

)

location

(

    'test.txt'

)

)REJECT LIMIT UNLIMITED




这样就可以利用生成的语句创建外部表，运行上面的语句创建外部表




运行查询外部表以验证外部表创建是否成功！




select * from "SYS_SQLLDR_X_EXT_TEST"




结果如下：




ID NAME

1 a

2 b

3 c

4 d

5 e

6 f




至此，外部表创建完毕！！！




<七>使用外部表实例:使用Oracle的外部表查询警告日志文件

对于DBA来说，最常见一个例子是可以使用外部表来访问警告日志文件或其他跟踪文件.

以下一个例子用来说明外部表的用途。

首先需要创建一个Directory：

[oracle@jumper oracle]$ sqlplus "/ as sysdba"




SQL*Plus: Release 9.2.0.4.0 - ProdUCtion on Sun Oct 15 21:42:28 2006




Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.







Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning option

JServer Release 9.2.0.4.0 - Production




SQL> create or replace directory bdump

2 as '/opt/oracle/admin/eygle/bdump';




Directory created.




SQL> col DIRECTORY_PATH for a30

SQL> col owner for a10

SQL> select * from dba_directories;




OWNER DIRECTORY_NAME DIRECTORY_PATH

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

SYS BDUMP /opt/oracle/admin/eygle/bdump







然后创建一个外部表:







SQL> create table alert_log ( text varchar2(400) )

2 organization external (

3 type oracle_loader

4 default directory BDUMP

5 Access parameters (

6 records delimited by newline

7 nobadfile

8 nodiscardfile

9 nologfile

10 )

11 location('alert_eygle.log')

12 )

13 reject limit unlimited

14 /




Table created.







然后我们就可以通过外部表进行查询警告日志的内容:

select * from alert_log where text like 'ORA-%';







以上。