测试TOM=SQLLDR生成外部表-优快云博客

本文详细介绍了如何使用SQLLDR生成外部表，包括创建外部表的过程、SQL语句生成、权限分配以及数据加载流程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

下面测试利用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

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

[oracle@oraclelinux ~]$ cat demo1.log

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

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

[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/