SQL*Loader使用方法

分類： Oracle 其它特性 2010-10-08 09:21 475人閱讀評論(0) 舉報

--=====================

-- SQL*Loader使用方法

--=====================

一、SQL*Loader的體系結構

SQL*Loader由一個輸入控制檔案來控制整個裝載的相關描述資訊，一個或多個資料檔案作為原始資料，其詳細組成結構包括

Input Datafiles --&gt裝載到資料庫的原始資料檔案

Loader Control file --&gt提供給QL*Loader尋找及翻譯資料的相關資訊

Log File --&gt裝載過程中產生的日誌資訊

Bad Files --&gt被剔除的一些不合乎規範化的資料，由SQL*Loader剔除，也可能被Oracle剔除

Discard Files --&gt對不滿足控制檔案中記錄選擇標準的一些物理記錄

以上五個完整的部分最終將資料匯入到資料庫，當然，部分元件可以省略。

二、控制檔案的作用及組成

控制檔案是一個文字檔案，控制檔案中記錄的資訊告訴SQL*Loader在哪裡尋找資料、如何翻譯資料，以及將資料插入到哪裡等

控制檔案的組成分為三個部分

第一部分主要是關於通外部會話的相關資訊

如一些全域性選項、行資訊、是否跳過特殊記錄等

infile子句指明瞭從哪裡尋找源資料

第二部分由一個或多個Into table塊，每一個塊包含一些被匯入表的相關資訊，如表名，列名等

第三部分為可選項，如果存在則包含匯入的源資料

控制檔案寫法的注意事項

語法結構自由

不區分大小寫

在行開始處使用--來作為註釋行，在控制檔案中的第三部分使用--來註釋不被支援

關鍵字constant 和zone被保留

三、資料檔案

資料檔案可以有多個，這些資料檔案需要在控制檔案中指定

從SQL*Loader角度來看，資料檔案中的資料被當做一條條記錄

一個資料檔案描述資料檔案記錄有三種可選的格式

固定記錄格式

可變記錄格式

流記錄格式

這些記錄格式在控制檔案使用infile引數時，如果記錄的格式未指定，則預設的為流記錄格式。如使用infile *時則為流記錄格式

下面給出幾種不同記錄格式的例子

a.固定格式：INFILE datafile_name "fix n"

load data

infile 'example.dat' "fix 11" --表明每條記錄長度固定為個位元組

into table example

fields terminated by ',' optionally enclosed by '"'

(col1, col2)

example.dat:

001, cd, 0002,fghi, --第一條記錄為, cd, 第二條記錄為,fghi, 其中第二條記錄包含了一個換行符

00003,lmn,

1, "pqrs",

0005,uvwx,

b.可變格式：INFILE "datafile_name" "var n"

load data

infile 'example.dat' "var 3" --使用3位來描述一條記錄的長度

into table example

fields terminated by ',' optionally enclosed by '"'

(col1 char(5),

col2 char(7))

example.dat:

009hello,cd,010world,im, --009 表明第一條記錄的長度為個9位元組，表明第二條記錄的長度為10個位元組等

012my,name is,

c.流記錄格式：INFILE datafile_name ["str terminator_string"]

load data

infile 'example.dat' "str '|/n'" --使用| 或換行符來作為一條記錄的終止

into table example

fields terminated by ',' optionally enclosed by '"'

(col1 char(5),

col2 char(7))

example.dat:

hello,world,|

james,bond,|

邏輯記錄的概念

通常情況下，資料檔案中的一條物理記錄即是一條邏輯記錄，即資料檔案的一條記錄對應於資料庫的一條記錄SQL*Loader擴充套件了該功能可以將多條物理記錄形成一條邏輯記錄，而由這個組合再來生成一條資料庫中的記錄

SQL*Loader支援兩種策略來形成邏輯記錄

組合固定條數的物理記錄來形成邏輯記錄

將滿足特定條件的物理記錄組合並形成邏輯記錄

四、資料檔案裝載方式

1.傳統路徑匯入

使用生成SQL Insert語句來處理源資料，並且透過commit提交儲存資料。每次資料匯入將產生一些事務

在插入資料時尋找可用資料塊，然後將資料填充到資料塊

在插入到分割槽表的單個分割槽時使用下面的語法

INSERT INTO TABLE T PARTITION (P) VALUES ...

基於多cpu系統使用多個裝載會話執行併發。即將資料檔案分割為多個來裝載

2.直接路徑匯入

直接將資料寫到Oracle資料檔案，並更所使用塊的高水位線標記來儲存資料

支援資料的並行匯入

直接路徑匯入期間，資料轉換髮生在客戶端而非伺服器端。即位於伺服器端引數檔案中NLS引數不會被使用

可以透過在控制檔案中設定NLS引數或設定伺服器端合適的環境變數，如下面的例子

HIREDATE DATE 'YYYYMMDD' --為控制檔案中的HIREDATE指定格式

% export NLS_DATE_FORMAT='YYYYMMDD' --在伺服器端設定NLS_DATE_FORMAT

直接路徑裝載單個分割槽或子分割槽，裝載期間的其它分割槽可以執行DML操作

LOAD INTO TABLE T PARTITION (P) VALUES ...

LOAD INTO TABLE T SUBPARTITION (P) VALUES ...

使用直接路徑裝載時，需要指定DIRECT=true

支援兩種不同的併發

1.同時裝載到分割槽表表的不同分割槽或同時裝載到不同的表

2.分成多個伺服器裝載到分割槽表的單個分割槽或單個表，最後將裝載的臨時段合併儲存到分割槽或表

3.兩者對比(下面描述中傳統匯入方式稱為前者，直接匯入方式稱為後者)

a.前者使用commit來儲存資料，後者更新高水位線標記儲存資料

b.前者產生redo記錄，後者基於特定的條件產生redo記錄

c.前者強制所有的約束，後者僅僅強制primary key,unique,not null約束

d.前者將觸發insert觸發器，後者不會觸發insert觸發器

e.前者支援簇表，後者不支援簇表

f.前者插入資料時其它使用者可以DML表，後者則不行

五、演示SQL*Loader

1.SQL*Loader可執行程式(sqlldr)所在的位置

[oracle@oradb ~]$ ls -lh $ORACLE_HOME/bin/sql*

-rwxr-x--x 1 oracle oinstall 634K Sep 13 20:01 /u01/app/oracle/10g/bin/sqlldr

2.檢視sqlldr的幫助資訊，

[oracle@oradb ~]$ sqlldr

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Sep 23 10:38:31 2010

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.

3.將資料檔案和控制檔案組合在一起

[oracle@oradb ~]$ cat sqlldr_demo/demo1.ctl

LOAD DATA

INFILE *

INTO TABLE DEPT

FIELDS TERMINATED BY ','

(DEPTNO, DNAME, LOC )

BEGINDATA

10,Sales,Virginia

20,Accounting,Virginia

30,Consulting,Virginia

40,Finance,Virginia

SQL> show user;

USER is "ROBINSON"

SQL> create table dept

2 (deptno number(2) constraint dept_pk primary key,

3 dname varchar2(20),

4 loc varchar2(20));

Table created.

[oracle@oradb sqlldr_demo]$ sqlldr robinson/robinson control=demo1.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Sep 23 11:29:34 2010

Commit point reached - logical record count 4

SQL> select * from dept;

DEPTNO DNAME LOC

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

10 Sales Virginia

20 Accounting Virginia

30 Consulting Virginia

40 Finance Virginia

4.將資料檔案和控制檔案分離實現資料裝載

[oracle@oradb sqlldr_demo]$ cat demo1.ctl demo1.data --檢視分離後兩者的內容

LOAD DATA

INFILE demo1.data

INTO TABLE DEPT

FIELDS TERMINATED BY ','

(DEPTNO, DNAME, LOC )

10,Sales,Virginia

20,Accounting,Virginia

30,Consulting,Virginia

40,Finance,Virginia

[oracle@oradb sqlldr_demo]$ sqlldr robinson/robinson control=demo1.ctl log=demo1.log

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Sep 23 12:21:35 2010

SQL*Loader-601: For INSERT option, table must be empty. Error on table DEPT

SQL> truncate table dept; --收到了SQL*Loader-601錯誤提示，清空原表

[oracle@oradb sqlldr_demo]$ sqlldr robinson/robinson control=demo1.ctl log=demo1.log

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Sep 23 12:22:39 2010

Commit point reached - logical record count 4 --再次正常匯入

SQL*Loader使用方法

SQL*Loader使用方法

相關文章