SQL*Loader 装载外部数据

最新推荐文章于 2021-10-30 11:35:11 发布

crbzijz188696507

最新推荐文章于 2021-10-30 11:35:11 发布

阅读量150

点赞数

一、创建外部表

--创建外部表存放目录

[oracle@gc1 ~]$ cd /home/oracle

[oracle@gc1 ~]$ mkdir dir

[oracle@gc1 ~]$ cd dir

[oracle@gc1 dir]$ pwd

/home/oracle/dir

--数据库创建对应文件目录，并赋予操作权限给所有用户

SQL> show user

USER is "SYS"

SQL> create directory dir as '/home/oracle/dir';

Directory created.

SQL> grant all on directory dir to public;

Grant succeeded

--查看directory信息

SQL> col owner for a10

SQL> col directory_name for a20

SQL> col directory_path for a50

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

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

SYS DATA_PUMP_DIR /u01/app/oracle/product/10.2.0/db_1/rdbms/log/

SYS DIR /home/oracle/dir

--创建外表部数据源

[oracle@gc1 dir]$ vi prod_my.da

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

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

--sqlloader方式创建外部表

参考官方创建案例如下：

按如下方式修改即可：

CREATE TABLE scott.prod_my (

c1 number(5),

c2 varchar2(20),

c3 varchar2(20),

c4 varchar2(30),

c5 number(5),

c6 date,

c7 number(8,3),

c8 number(5,2),

c9 number(5,2),

c10 varchar2(30)

)

ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY dir

ACCESS PARAMETERS (FIELDS TERMINATED BY ","

OPTIONALLY ENCLOSED BY '"'

MISSING FIELD VALUES ARE NULL)

LOCATION (' prod_my.data '));

--执行上面的sqlload建外部表语句

SQL> CREATE TABLE scott.prod_my (

2 c1 number(5),

3 c2 varchar2(20),

4 c3 varchar2(20),

5 c4 varchar2(30),

6 c5 number(5),

7 c6 date,

8 c7 number(8,3),

9 c8 number(5,2),

10 c9 number(5,2),

11 c10 varchar2(30)

12 )

13 ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY dir

14 ACCESS PARAMETERS (FIELDS TERMINATED BY ","

15 OPTIONALLY ENCLOSED BY '"'

16 MISSING FIELD VALUES ARE NULL)

17 LOCATION ('prod_my.data'));

Table created.

SQL> select * from scott.prod_my;

C1 C2 C3 C4 C5 C6 C7 C8 C9 C10

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

360 Jane Janus ST_CLERK 121 17-MAY-01 3000 0 50 jjanus

361 Mark Jasper SA_REP 145 17-MAY-01 8000 .1 80 mjasper

362 Brenda Starr AD_ASST 200 17-MAY-01 5500 0 10 bstarr

363 Alex Alda AC_MGR 145 17-MAY-01 9000 .15 80 aalda

401 Jesse Cromwell HR_REP 203 17-MAY-01 7000 0 40 jcromwel

402 Abby Applegate IT_PROG 103 17-MAY-01 9000 .2 60 aapplega

403 Carol Cousins AD_VP 100 17-MAY-01 27000 .3 90 ccousins

404 John Richardson AC_ACCOUNT 205 17-MAY-01 5000 0 110 jrichard

8 rows selected.

二、sqlloader导入外部数据

--参考官方sqlloader控制文件案例

做以下修改，创建控制文件ldr.ctl

[oracle@gc1 dir]$ pwd

/home/oracle/dir

[oracle@gc1 dir]$ ls

PROD_MY_12177.log PROD_MY_14213.log PROD_MY_14214.log prod_my.data

[oracle@gc1 dir]$ vi ldr.ctl

load data

infile ' prod_my.data ' --在此定义外部数据文件

append --导入数据时，直接接着原表中已有数据往后插入

into table scott.zhen_ldr --要导入的实体表

fields terminated by ','

optionally enclosed by '"'

TRAILING NULLCOLS --去除空行

(

c1,

c2,

c3,

c4,

c5,

c6,

c7,

c8,

c9,

c10 --导入实体表的列名

)

注意：sqlloader控制文件中不能有；号，要有外部数据文件、实体表。各关键字代表含义详见上面--注释内容。

--查看ldr.ctl信息

[oracle@gc1 dir]$ more ldr.ctl

load data

infile 'prod_my.data'

append

into table scott.zhen_ldr

fields terminated by ','

optionally enclosed by '"'

TRAILING NULLCOLS

(

c1,

c2,

c3,

c4,

c5,

c6,

c7,

c8,

c9,

c10

)

--创建ldr.ctl中定义的外部数据要导入的表scott.zhen_ldr

SQL> create table scott.zhen_ldr(

2 c1 number(5),

3 c2 varchar2(20),

4 c3 varchar2(20),

5 c4 varchar2(30),

6 c5 number(5),

7 c6 date,

8 c7 number(8,3),

9 c8 number(5,2),

10 c9 number(5,2),

11 c10 varchar2(30)

12 );

Table created.

--prod_my.data数据文件中增加空行

[oracle@gc1 dir]$ vi prod_my.data

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

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

"prod_my.data" 10L, 482C written

--查看sqlloader语法

[oracle@gc1 dir]$ sqlldr

SQL*Loader: Release 10.2.0.1.0 - Production on Mon Apr 28 04:14:27 2014

Usage: SQLLDR keyword=value [,keyword=value,...]

Valid Keywords:

userid -- ORACLE username/password

control -- control file name

log -- log file name

bad -- bad file name

data -- data file name

discard -- discard file name

discardmax -- number of discards to allow (Default all)

skip -- number of logical records to skip (Default 0)

load -- number of logical records to load (Default all)

errors -- number of errors to allow (Default 50)

rows -- number of rows in conventional path bind array or between direct path data saves

(Default: Conventional path 64, Direct path all)

bindsize -- size of conventional path bind array in bytes (Default 256000)

silent -- suppress messages during run (header,feedback,errors,discards,partitions)

direct -- use direct path (Default FALSE)

parfile -- parameter file: name of file that contains parameter specifications

parallel -- do parallel load (Default FALSE)

file -- file to allocate extents from

skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Default FALSE)

skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE)

commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE)

readsize -- size of read buffer (Default 1048576)

external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_USED)

columnarrayrows -- number of rows for direct path column array (Default 5000)

streamsize -- size of direct path stream buffer in bytes (Default 256000)

multithreading -- use multithreading in direct path

resumable -- enable or disable resumable for current session (Default FALSE)

resumable_name -- text string to help identify resumable statement

resumable_timeout -- wait time (in seconds) for RESUMABLE (Default 7200)

date_cache -- size (in entries) of date conversion cache (Default 1000)

PLEASE NOTE: Command-line parameters may be specified either by

position or by keywords. An example of the former case is 'sqlldr

scott/tiger foo'; an example of the latter is 'sqlldr control=foo

userid=scott/tiger'. One may specify parameters by position before

but not after parameters specified by keywords. For example,

'sqlldr scott/tiger control=foo logfile=log' is allowed, but

'sqlldr scott/tiger control=foo log' is not, even though the

position of the parameter 'log' is correct.

注意：参数很多，实际只要userid、control即可。

--执行sqlloader导入外部数据

[oracle@gc1 dir]$ sqlldr userid=scott/tiger control=ldr.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon Apr 28 04:17:17 2014

Commit point reached - logical record count 10

[oracle@gc1 dir]$

--查看scott.zhen_ldr表中数据信息

SQL> set linesize 200;

SQL> select * from scott.zhen_ldr;