Oracle SQL Loader(sqlldr)+ Externale Tables

(1)使用SQLLDR將外部資料插入到資料庫裡
(2)使用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

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.

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！