测试TOM=SQLLDR生成外部表

什么是外部表--简单说就是可以把操作系统文件当成是一个只读的数据库表

 

下面测试利用SQLLDR来生成外部表

测试开始

[oracle@oraclelinux ~]$ cat demo1.ctl

LOAD DATA

INFILE *

INTO TABLE DEPT_LOAD

FIELDS TERMINATED BY ','

(DEPTNO, DNAME, LOC )

BEGINDATA

10,Sales,Virginia

20,Accounting,Virginia

30,Consulting,Virginia

40,Finance,Virginia

[oracle@oraclelinux ~]$ sqlldr scott/scott control=demo1.ctl external_table=generate_only;

 

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 16:51:06 2012

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

以上部分为外部表进行了定义，查看日志文件可以发现实际的SQL语句是怎么样的

 

[oracle@oraclelinux ~]$ cat demo1.log

 

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 16:51:06 2012

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

Control File:   demo1.ctl

Data File:      demo1.ctl

  Bad File:     demo1.bad

  Discard File:  none specified

 

 (Allow all discards)

 

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Continuation:    none specified

Path used:      External Table

 

Table DEPT_LOAD, loaded from every logical record.

Insert option in effect for this table: INSERT

 

   Column Name                  Position   Len  Term Encl Datatype

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

DEPTNO                              FIRST     *   ,       CHARACTER            

DNAME                                NEXT     *   ,       CHARACTER            

LOC                                  NEXT     *   ,       CHARACTER            

 

 

 

CREATE DIRECTORY statements needed for files

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

CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/u01/oracle'【在生成外部表脚本期间，SQLLDR连接到数据库，并查询数据字典查看是否已经存在合适的目录，在这里因为没有合适的目录所以生成了一个CREATE DIRECTORY语句】

 

 

CREATE TABLE statement for external table:

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

CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT_LOAD" 

(

  "DEPTNO" NUMBER(2),

  "DNAME" VARCHAR2(14),

  "LOC" VARCHAR2(13)

)  【这是生成的创建外部表的语句】

ORGANIZATION external 

(

  TYPE oracle_loader

  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000【这一部分通知数据库表不是正常表，不是普通的HEAP表，也不是IOT表，是外部表】

  ACCESS PARAMETERS 

  (

    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII

    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo1.bad'

    LOGFILE 'demo1.log_xt'

    READSIZE 1048576

    SKIP 6

    FIELDS TERMINATED BY "," LDRTRIM 

    REJECT ROWS WITH ALL NULL FIELDS 

    (

      "DEPTNO" CHAR(255)

        TERMINATED BY ",",

      "DNAME" CHAR(255)

        TERMINATED BY ",",

      "LOC" CHAR(255)

        TERMINATED BY ","

    )

  )  【这一部分通知数据库如何处理输入文件】

  location 

  (

    'demo1.ctl'

  )

)REJECT LIMIT UNLIMITED【这通知数据库所加载的文件名是什么，这里指DEMO1.CTL】

 

 

INSERT statements used to load internal tables:

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

INSERT /*+ append */ INTO DEPT_LOAD 

(

  DEPTNO,

  DNAME,

  LOC

)

SELECT 

  "DEPTNO",

  "DNAME",

  "LOC"

FROM "SYS_SQLLDR_X_EXT_DEPT_LOAD"[这一部分是生成的INSERT语句，可用于从外部表本身加载数据]

 

 

statements to cleanup objects created by previous statements:

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

DROP TABLE "SYS_SQLLDR_X_EXT_DEPT_LOAD"

DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000【加载完成之后删除这前创建的一些对象】

 

 

 

Run began on Mon May 14 16:51:06 2012

Run ended on Mon May 14 16:51:06 2012

 

Elapsed time was:     00:00:00.48

CPU time was:         00:00:00.07

[oracle@oraclelinux ~]$ 

 

接下来为SCOTT用户分配适当权限，测试外部表

 SQL> show user;

USER is "SYS"

SQL> grant create any directory to scott;

Grant succeeded.

SQL> grant drop any directory to scott;

Grant succeeded.

SQL> select * from dept_load;

no rows selected

从外部表加载数据到DEPT_LOAD;

[oracle@oraclelinux ~]$ sqlldr scott/scott control=demo1.ctl external_table=execute;

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 17:45:30 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

[oracle@oraclelinux ~]$ exit

exit

SQL> select * from dept_load;

    DEPTNO DNAME          LOC           ENTIRE_LINE                   LAST_UPDA

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

COMMENTS

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

        10 Sales          Virginia

        20 Accounting     Virginia

        30 Consulting     Virginia

    DEPTNO DNAME          LOC           ENTIRE_LINE                   LAST_UPDA

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

COMMENTS

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

        40 Finance        Virginia

SQL> 

测试结束

 

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

转载于:http://blog.itpub.net/15720542/viewspace-723555/