oracle ldr 到处,oracle 外部表之 sqlldr 生成

oracle的导入工具,速度挺快,这里记一下外部表的使用,通常外部表要记的语法太多例如:

CREATE TABLE PROD_MASTER

(

"EMPNO" NUMBER,

"ENAME" VARCHAR2(50),

"HIREDATE" DATE,

"DEPTNO" NUMBER

)

ORGANIZATION external

(

TYPE oracle_loader

DEFAULT DIRECTORY ext_table

ACCESS PARAMETERS

(

RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII

preprocessor ext_table:'uncompress.sh'

BADFILE 'EXT_TABLE':'prod_master.dat.bad'

LOGFILE '1.log_xt'

READSIZE 1048576

FIELDS TERMINATED BY "," LDRTRIM

MISSING FIELD VALUES ARE NULL

REJECT ROWS WITH ALL NULL FIELDS

(

"EMPNO" CHAR(255)

TERMINATED BY ",",

"ENAME" CHAR(255)

TERMINATED BY ",",

"HIREDATE" CHAR(10)

TERMINATED BY ","

DATE_FORMAT DATE MASK 'dd-mon-yyyy',

"DEPTNO" CHAR(255)

TERMINATED BY ","

)

)

location

(

'prod_master.dat.gz'

)

)REJECT LIMIT UNLIMITED

如果全靠手打,这个工作量有点大,可以考虑系统自己生成,步骤如下:

1 检查组件:

SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

select comp_id,comp_name, version, status from dba_registry;

chopt disable dv

如果不关

SQL> select * from prod_master;

select * from prod_master

*

ERROR at line 1:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout

ORA-29400: data cartridge error

KUP-04094: preprocessing cannot be performed if Database Vault is installed

2 创建表

CREATE TABLE PROD_MASTER

(

"EMPNO" NUMBER,

"ENAME" VARCHAR2(50),

"HIREDATE" DATE,

"DEPTNO" NUMBER

)

3 创建控制文件

cat prod_master.ctl

load data

infile '/home/oracle/scripts/prod_master.dat.gz'

append

into table prod_master

fields terminated by ","

trailing nullcols

(empno,ename,hiredate date(10) 'dd-mon-yyyy',deptno)

4创建存放目录

create directory ext as '/home/oracle/ext';

grant read,write,execute on ext;

5 生成建外部表的语句

sqlldr userid=hr/hr control=prod_master.ctl extern_table=gernerate_only log=create_table.sql

由于启用了压缩,修改其中:

PREPROCESSOR EXT:'uncompress.sh' --Preprocessor before readsize