SQL*Loader
SQL*Loader
1外部表简介
⑴外部表并不实际存在，外部文件中的数据字段只是映射的外部表列，并不是实际装载到其中
⑵涉及外部表，只能用select,不能用insert、update、delete
⑶外部表是只读表，不能进行索引
2.SQL*Loader生成外部表创建语句
external_table=not_used 默认值
external_table=generate_only不装载任何数据，
而是生成控制文件中描述的装载外部表所需的所有SQL语句，
并将这些SQL语句放入SQL*Loader日志文件
external_table=execute 执行这些SQL语句以创建外部表并进行装载
external_table=generate_only选项在SQL*Loader日志文件中输出一下信息：
①create directory语句
②外部表的完整create table语句，并带有必要的access parameters
③装载Oracle表的insert语句
④目录和外部表的delete语句
创建test_emp表
SCOTT@ora11g>select dbms_metadata.get_ddl('TABLE','EMP') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
SCOTT@ora11g>
CREATE TABLE test_emp
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP1" PRIMARY KEY ("EMPNO"))
TABLESPACE USERS;
创建SQL*Loader的控制文件
Oracle$cd /u02/app/oracle
test.ctl
load data
infile *
into table test_emp
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
begindata
7369,”SMITH”,”CLERK”,7902,17-DEC-80,800,20
sqlldr scott/tiger \
control=test.ctl \
external_table=generate_only
[oracle@pc2 u02]$ sqlldr scott/tiger control=test.ctl external_table=generate_only
SQL*Loader: Release 11.2.0.2.0 - Production on Sat Dec 28 18:50:50 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
[oracle@pc2 oracle]$ pwd
/u02/app/oracle
[oracle@pc2 oracle]$ ls
test.ctltest.log
[oracle@pc2 oracle]$ cat test.log
SQL*Loader: Release 11.2.0.2.0 - Production on Sat Dec 28 22:14:52 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Control File: test.ctl
Data File: test.ctl
Bad File: test.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 TEST_EMP, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO FIRST * , O(") CHARACTER
ENAME NEXT * , O(") CHARACTER
JOB NEXT * , O(") CHARACTER
MGR NEXT * , O(") CHARACTER
HIREDATE NEXT * , O(") CHARACTER
SAL NEXT * , O(") CHARACTER
COMM NEXT * , O(") CHARACTER
DEPTNO NEXT * , O(") CHARACTER
CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/u02/app/oracle'
CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_TEST_EMP"
(
"EMPNO" NUMBER(4),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2)
)
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':'test.bad'
LOGFILE 'test.log_xt'
READSIZE 1048576
SKIP 7
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"EMPNO" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"ENAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"JOB" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"MGR" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"HIREDATE" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"SAL" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"COMM" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"DEPTNO" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'test.ctl'
)
)REJECT LIMIT UNLIMITED
INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO TEST_EMP
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
)
SELECT
"EMPNO",
"ENAME",
"JOB",
"MGR",
"HIREDATE",
"SAL",
"COMM",
"DEPTNO"
FROM "SYS_SQLLDR_X_EXT_TEST_EMP"
statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_TEST_EMP"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
Run began on Sat Dec 28 22:14:52 2013
Run ended on Sat Dec 28 22:14:53 2013
Elapsed time was: 00:00:00.50
CPU time was: 00:00:00.04
-------------------------------------------------------------------------------------------------------------------------------------------
3使用外部表装载数据
数据装载，从外部表装载Oracle表，insert into …… select
数据卸载,使用Oracle表数据填充外部表，create table …. as select
①创建目录对象及其位置
SYS@ora11g>CREATE DIRECTORY dir AS '/u02/app/oracle/ss';
Directory created.
SYS@ora11g>grant read,write on directory dir to scott;
Grant succeeded.
②访问驱动器（access driver），保证外部数据的处理与外部表的描述相匹配
ORACLE_LOADER,只能装载
ORACLE_DATAPUMP,可装载，卸载
③创建外部表
SCOTT@ora11g>
CREATE TABLE t1
(
"EMPNO" NUMBER(4),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE 'test.bad'
LOGFILE 'test.log_xt'
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
)
)
location
(
'text.dat'
)
)REJECT LIMIT UNLIMITED;
Vim /u02/app/oracle/ss/text.dat
④查询外部表的数据
SCOTT@ora11g>select * from t1;
CREATE TABLE test_emp
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP1" PRIMARY KEY ("EMPNO"))
TABLESPACE USERS;
⑤通过外部表从test.dat表中装载数据到数据库的test_emp表中
SCOTT@ora11g>
INSERT /*+ append */ INTO TEST_EMP
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
)
SELECT
"EMPNO",
"ENAME",
"JOB",
"MGR",
“HIREDATE”,
"SAL",
"COMM",
"DEPTNO"
FROM t1;
⑥
SCOTT@ora11g>select * from TEST_EMP;
⑦commit;
⑧DROP TABLEt1;
DROP DIRECTORYdir;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4.sqlldr加载外部数据
[oracle@pc2 u02]$ pwd
/u02
[oracle@pc2 u02]$ ls
prod_master.ctl prod_master.dat prod_master.logprod_master.bad
写控制文件prod_master.ctl要以.ctl结尾
load data
infile '/u02/prod_master.dat'文件要以.dat结尾
append into table AB
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(ProdID,ProdName,Code,ReOrder,Cost,Price,PriSrc,SecSrc,Obsoleted Date "dd-mon-yyyy")指的是.dat文件的日期格式,此日期格式可以与数据库的不同
create table AB (
ProdID number(4),
ProdName varchar2(100),
Code varchar2(50),
ReOrder varchar2(30),
Cost varchar2(30),
Price varchar2(30),
PriSrc varchar2(30),
SecSrc varchar2(30),
Obsoleted Date
) tablespace users;
sqlldr \‘sys/oracle as sysdba\’ control=prod_master.ctl
prod_master.dat
1001,Oracle Database 10g,server,25,45.45,14.32,Redwood Shores,Reston,
1002,Oracle9i Database,server,15,55.78,50.10,Redwood Shores,Reston,
1003,Oracle8.0 Database,server,7,67.14,54.40,Redwood Shores,Reston,14-FEB-2001,
1004,Oracle Application Server 10g,server,25,92.87,89.13,Redwood Shores,Reston,
1005,Oracle Internet Application Server 9i,server,15,10.95,9.95,Redwood Shores,Reston,
1006,Oracle JDeveloper,tools,5,78.78,81.15,Redwood Shores,Reston,
1007,Oracle Developer,tools,10,32.32,30.22,Redwood Shores,Reston,
1008,Oracle Reports,tools,3,45.21,40.18,Redwood Shores,Reston,
1009,Oracle Discoverer,tools,7,12.47,10.69,Redwood Shores,Reston,
1010,Oracle Collaborations Suite,server,25,97.32,90.12,Redwood Shores,Reston,
1011,Oracle Real Application Clusters,server,30,7.21,6.15,Redwood Shores,Reston,
1012,Oracle Financial Applications,apps,8,1.99,0.87,Redwood Shores,Reston,
1013,Oracle Human Resource Applications,apps,9,92.87,89.14,Redwood Shores,Reston,
1014,Oracle Business Intelligence,apps,11,104.21,72.34,Redwood Shores,Reston,
1015,Oracle Maintenance Management,apps,14,12.31,8.14,Redwood Shores,Reston,
1016,Oracle Manufacturing,apps,3,34.56,34.55,Redwood Shores,Reston,
1017,Oracle Order Management,apps,7,56.67,56.72,Redwood Shores,Reston,
1018,Oracle Procurement,apps,16,67.78,60.01,Redwood Shores,Reston,
1019,Oracle Marketing and Sales,apps,9,78.89,75.97,Redwood Shores,Reston,
1020,Oracle Project Management,apps,10,12.23,11.79,Redwood Shores,Reston,
1021,Oracle Inventory Optimization,apps,2,23.34,12.14,Redwood Shores,Reston,
1022,Oracle Supply Chain Management,apps,1,34.45,31.63,Redwood Shores,Reston,
1023,Oracle Forms,tools,19,45.56,41.75,Redwood Shores,Reston,
1024,Oracle Depot Repair,apps,2,56.67,49.34,Redwood Shores,Reston,
1025,Oracle Tutor,apps,1,67.78,61.17,Redwood Shores,Reston,
1026,Oracle iStore,apps,9,78.89,75.00,Redwood Shores,Reston,
1027,Oracle Outsourcing,services,3,89.90,85.90,Redwood Shores,Reston,
1028,Oracle Consulting,services,35,12.23,12.14,Redwood Shores,Reston,
1029,Oracle University,services,50,23.34,21.23,Redwood Shores,Reston,
1030,Oracle Support,services,32,18.74,17.55,Colorado Springs,Reston,
1000,Oracle Database 7.3,server,1,35.12,28.73,Redwood Shores,Reston,01-JAN-2000,
