Oracle SQL Loader(sqlldr)+ Externale Tables

最新推荐文章于 2024-05-30 14:59:09 发布

ckth47350

最新推荐文章于 2024-05-30 14:59:09 发布

阅读量552

点赞数

文章标签：数据库

Oracle SQL Loader(sqlldr)+ Externale Tables

(1)使用SQLLDR将外部数据插入到数据库里
(2)使用SQLLDR提供外部表的定义并创建外部表

(1)使用SQLLDR将外部数据插入到数据库里

1 查看控制文件
[oracle@chen scripts]$ cat prod_master.ctl01
LOAD DATA
INFILE '/home/oracle/scripts/prod_master.dat'
BADFILE '/home/oracle/scripts/prod_master.bad'
DISCARDFILE '/home/oracle/scripts/prod_master.dsc'
---PREPROCESSOR execdir:'/home/oracle/scripts/uncompress.sh'
APPEND
INTO TABLE sh.prod_master
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(sname, cname, sno, cno, cname1, cname2, sdate,corder,sorder)

2 创建模板表
SQL> conn sh/sh
SQL>
create table prod_master
(sname varchar2(200),
cname varchar2(200),
sno number(20),
cno number(20),
cname1 varchar2(200),
cname2 varchar2(200),
sdate varchar2(200),
corder varchar2(200),
sorder varchar2(200));

3 查看数据
[oracle@chen scripts]$ zcat prod_master.dat.gz
A,AA,1,11,AAA,AAAA,2014-09-09
B,BB,2,22,BBB,BBBB,2014-09-09,20,45
C,CC,3,33,CCC,CCCC,2014-09-09,40
D,DD,4,44,DDD,DDDD,2014-09-09
E,EE,5,55,EEE,"EEEE",2014-09-09
F,FF,6,66,FFF,"FF,FF","2014-09-09"
G,GG,7,77,ggg,CIH

[oracle@chen scripts]$ gunzip prod_master.dat.gz

4 通过sqlldr加载数据
[oracle@chen scripts]$ sqlldr sh/sh control=prod_master.ctl01

SQL*Loader: Release 11.2.0.4.0 - Production on Mon Jul 10 21:59:05 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 7

5 验证数据
SQL> set linesize 1000
SQL> select * from prod_master;
SNAME CNAME SNO CNO CNAME1 CNAME2 SDATE CORDER SORDER
-------------------- -------------------- ---------- ---------- -------------------- -------------------- -------------------- -------------------- --------------------
A AA 1 11 AAA AAAA 2014-09-09
B BB 2 22 BBB BBBB 2014-09-09 20 45
C CC 3 33 CCC CCCC 2014-09-09 40
D DD 4 44 DDD DDDD 2014-09-09
E EE 5 55 EEE EEEE 2014-09-09
F FF 6 66 FFF FF,FF 2014-09-09
G GG 7 77 ggg CIH

7 rows selected.

6 查看表段
SQL> col segment_name for a20
SQL> select segment_name,bytes from user_segments;
SEGMENT_NAME BYTES
-------------------- ----------
PROD_MASTER 65536

(2)使用SQLLDR提供外部表的定义并创建外部表

Oracle SQL Loader(sqlldr) + Externale Tables

1 查看控制文件
[oracle@chen scripts]$ cat prod_master.ctl
LOAD DATA
INFILE '/home/oracle/scripts/prod_master.dat'
BADFILE '/home/oracle/scripts/prod_master.bad'
DISCARDFILE '/home/oracle/scripts/prod_master.dsc'
---PREPROCESSOR execdir:'/home/oracle/scripts/uncompress.sh'
APPEND
INTO TABLE sh.prod_master
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(sname, cname, sno, cno, cname1, cname2, sdate,corder,sorder)

2 查看数据
[oracle@chen scripts]$ zcat prod_master.dat
A,AA,1,11,AAA,AAAA,2014-09-09
B,BB,2,22,BBB,BBBB,2014-09-09,20,45
C,CC,3,33,CCC,CCCC,2014-09-09,40
D,DD,4,44,DDD,DDDD,2014-09-09
E,EE,5,55,EEE,"EEEE",2014-09-09
F,FF,6,66,FFF,"FF,FF","2014-09-09"
G,GG,7,77,ggg,CIH

[oracle@chen scripts]$ gunzip prod_master.dat

3 创建prod_master 模板表。
如果不提前创建prod_master 模板表，在执行通过SQLloader 生成创建外部表的Log时会报如下错误：
SQL*Loader-941: Error during describe of table SH.PROD_MASTER
ORA-04043: object SH.PROD_MASTER does not exist
SQL> conn sh/sh
SQL> create table prod_master
(sname varchar2(20),
cname varchar2(20),
sno number(10),
cno number(10),
cname1 varchar2(20),
cname2 varchar2(20),
sdate varchar2(20),
corder varchar2(20),
sorder varchar2(20));

Table created.

4 使用SQLloader 生成创建外部表的Log:
[oracle@chen scripts]$ sqlldr sh/sh control=prod_master.ctl external_table=generate_only log=external0711.log

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Jul 11 09:20:33 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

5 生成创建外部表的Log如下：
[oracle@chen scripts]$ cat external0711.log
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Jul 11 09:20:33 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Control File: prod_master.ctl
Data File: /home/oracle/scripts/prod_master.dat
Bad File: /home/oracle/scripts/prod_master.bad
Discard File: /home/oracle/scripts/prod_master.dsc
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: External Table

Table SH.PROD_MASTER, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
SNAME FIRST * , O(") CHARACTER
CNAME NEXT * , O(") CHARACTER
SNO NEXT * , O(") CHARACTER
CNO NEXT * , O(") CHARACTER
CNAME1 NEXT * , O(") CHARACTER
CNAME2 NEXT * , O(") CHARACTER
SDATE NEXT * , O(") CHARACTER
CORDER NEXT * , O(") CHARACTER
SORDER NEXT * , O(") CHARACTER

CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/scripts/'

CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_PROD_MASTER"
(
"SNAME" VARCHAR2(20),
"CNAME" VARCHAR2(20),
"SNO" NUMBER(10),
"CNO" NUMBER(10),
"CNAME1" VARCHAR2(20),
"CNAME2" VARCHAR2(20),
"SDATE" VARCHAR2(20),
"CORDER" VARCHAR2(20),
"SORDER" VARCHAR2(20)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'prod_master.bad'
DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'prod_master.dsc'
LOGFILE 'external0711.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"SNAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"CNAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"SNO" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"CNO" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"CNAME1" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"CNAME2" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"SDATE" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"CORDER" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"SORDER" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'prod_master.dat'
)
)REJECT LIMIT UNLIMITED

INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO SH.PROD_MASTER
(
SNAME,
CNAME,
SNO,
CNO,
CNAME1,
CNAME2,
SDATE,
CORDER,
SORDER
)
SELECT
"SNAME",
"CNAME",
"SNO",
"CNO",
"CNAME1",
"CNAME2",
"SDATE",
"CORDER",
"SORDER"
FROM "SYS_SQLLDR_X_EXT_PROD_MASTER"

statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_PROD_MASTER"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

Run began on Tue Jul 11 09:20:33 2017
Run ended on Tue Jul 11 09:20:33 2017

Elapsed time was: 00:00:00.52
CPU time was: 00:00:00.03

6 编写创建外部表的脚本
[oracle@chen scripts]$ touch create_prod.sql
CREATE TABLE "PROD_MASTER"
(
"SNAME" VARCHAR2(20),
"CNAME" VARCHAR2(20),
"SNO" NUMBER(10),
"CNO" NUMBER(10),
"CNAME1" VARCHAR2(20),
"CNAME2" VARCHAR2(20),
"SDATE" VARCHAR2(20),
"CORDER" VARCHAR2(20),
"SORDER" VARCHAR2(20)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'prod_master.bad'
DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'prod_master.dsc'
LOGFILE 'external0711.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"SNAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"CNAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"SNO" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"CNO" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"CNAME1" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"CNAME2" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"SDATE" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"CORDER" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"SORDER" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'prod_master.dat'
)
)REJECT LIMIT UNLIMITED;

7 创建所需目录；
SQL> CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/scripts/';
Directory created.
SQL> grant read,write,execute on directory "SYS_SQLLDR_XT_TMPDIR_00000" to public;
Grant succeeded.

8 执行创建外部表的脚本
SQL> conn sh/sh
Connected.
SQL> drop table prod_master purge;
Table dropped.

SQL> @create_prod.sql
Table created.

9 验证数据
SQL> set linesize 1000
SQL> select * from prod_master;
SNAME CNAME SNO CNO CNAME1 CNAME2 SDATE CORDER SORDER
-------------------- -------------------- ---------- ---------- -------------------- -------------------- -------------------- -------------------- --------------------
A AA 1 11 AAA AAAA 2014-09-09
B BB 2 22 BBB BBBB 2014-09-09 20 45
C CC 3 33 CCC CCCC 2014-09-09 40
D DD 4 44 DDD DDDD 2014-09-09
E EE 5 55 EEE EEEE 2014-09-09
F FF 6 66 FFF FF,FF 2014-09-09
G GG 7 77 ggg CIH

7 rows selected.

10 外部表数据特点
(1) 外部表不会存在对应的表段
SQL> select * from user_segments;
no rows selected

(2) 无法进行DML操作
SQL> delete prod_master where sname='A';
delete prod_master where sname='A'
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table

(3) 无法通过analyze收集外部表的统计信息，但可以通过dbms_stats进行收集
SQL> analyze table prod_master compute statistics;
analyze table prod_master compute statistics
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table

SQL> exec dbms_stats.gather_schema_stats('SH');
PL/SQL procedure successfully completed.

(4 ) 无法对外部表进行truncate,但可以执行drop操作
SQL> truncate table prod_master;
truncate table prod_master
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table

SQL> drop table prod_master purge;
Table dropped.

SQL*Loader更详细的信息见下面的连接

SQL*Loader Command-Line Reference

http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_params.htm#SUTIL004

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

转载于:http://blog.itpub.net/29785807/viewspace-2141889/

ckth47350

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle SQL Loader(sqlldr)+ Externale Tables

Oracle SQL Loader(sqlldr)+ Externale Tables (1)使用SQLLDR将外部数据插入到数据库里 (2)使用SQLLDR提供外部表的定义并创建外部表 ...
复制链接

扫一扫