熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

oraclesqlloader全攻略

2013-11-13 15:51:09  來源: Oracle 

sql loader 的特點
oracle自己帶了很多的工具可以用來進行數據的遷移備份和恢復等工作但是每個工具都有自己的特點
比如說exp和imp可以對數據庫中的數據進行導出和導出的工作是一種很好的數據庫備份和恢復的工具因此主要用在數據庫的熱備份和恢復方面有著速度快使用簡單快捷的優點同時也有一些缺點比如在不同版本數據庫之間的導出導入的過程之中總會出現這樣或者那樣的問題這個也許是oracle公司自己產品的兼容性的問題吧
sql loader 工具卻沒有這方面的問題它可以把一些以文本格式存放的數據順利的導入到oracle數據庫中是一種在不同數據庫之間進行數據遷移的非常方便而且通用的工具缺點就速度比較慢另外對blob等類型的數據就有點麻煩了

sql loader 的幫助

  C:\>sqlldr

  SQL*Loader: Release Production on 星期六 ::

  Copyright (c) Oracle Corporation All rights reserved

  用法: SQLLDR keyword=value [keyword=value]

  有效的關鍵字:

  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 (全部默認)
skip Number of logical records to skip (默認)
load Number of logical records to load (全部默認)
errors Number of errors to allow (默認)
rows Number of rows in conventional path bind array or between direct p
ath data saves
(默認: 常規路徑 所有直接路徑)
bindsize Size of conventional path bind array in bytes(默認)
silent Suppress messages during run (headerfeedbackerrorsdiscardspart
itions)
direct use direct path (默認FALSE)
parfile parameter file: name of file that contains parameter specification
s
parallel do parallel load (默認FALSE)
file File to allocate extents from
skip_unusable_indexes disallow/allow unusable indexes or index partitions(默
認FALSE)
skip_index_maintenance do not maintain indexes mark affected indexes as unus
able(默認FALSE)
readsize Size of Read buffer (默認)
external_table use external table for load; NOT_USED GENERATE_ONLY EXECUTE(
默認NOT_USED)
columnarrayrows Number of rows for direct path column array(默認)
streamsize Size of direct path stream buffer in bytes(默認)
multithreading use multithreading in direct path
resumable enable or disable resumable for current session(默認FALSE)
resumable_name text string to help identify resumable statement
resumable_timeout wait time (in seconds) for RESUMABLE(默認)
date_cache size (in entries) of date conversion cache(默認)

  PLEASE NOTE: 命令行參數可以由位置或關鍵字指定
前者的例子是 sqlload
scott/tiger foo; 後一種情況的一個示例是 sqlldr control=foo
userid=scott/tiger位置指定參數的時間必須早於
但不可遲於由關鍵字指定的參數例如
允許 sqlldr scott/tiger control=foo logfile=log 但是
不允許 sqlldr scott/tiger control=foo log 即使
參數 log 的位置正確

  C:\>

  三sql loader使用例子
a)SQLLoader將 Excel 數據導出到 Oracle
創建SQL*Loader輸入數據所需要的文件均保存到C:\用記事本編輯
控制文件inputctl內容如下

   load data           控制文件標識
   infile testtxt       要輸入的數據文件名為testtxt
   append into table test    向表test中追加記錄
   fields terminated by X  字段終止於X是一個制表符(TAB)
   (idusernamepasswordsj)   定義列對應順序

ainsert為缺省方式在數據裝載開始時要求表為空
bappend在表中追加新記錄
creplace刪除舊記錄替換成新裝載的記錄
dtruncate同上

在DOS窗口下使用SQL*Loader命令實現數據的輸入

C:\>sqlldr userid=system/manager control=inputctl
默認日志文件名為inputlog
默認壞記錄文件為inputbad

還有一種方法
可以把EXCEL文件另存為CSV(逗號分隔)(*csv)控制文件就改為用逗號分隔
LOAD DATA
INFILE d:\carcsv
APPEND INTO TABLE t_car_temp
FIELDS TERMINATED BY
(phonenovip_car)

  b)在控制文件中直接導入數據

  控制文件testctl的內容
The format for executing this file with SQL Loader is:
SQLLDR control=<filename> Be sure to substitute your
version of SQL LOADER and the filename for this file
LOAD DATA
INFILE *
BADFILE C:\Documents and Settings\Jackey\桌面\WMCOUNTRYBAD
DISCARDFILE C:\Documents and Settings\Jackey\桌面\WMCOUNTRYDSC
INSERT INTO TABLE EMCCOUNTRY
Fields terminated by ; Optionally enclosed by
(
COUNTRYID NULLIF (COUNTRYID=NULL)
COUNTRYCODE
COUNTRYNAME
CONTINENTID NULLIF (CONTINENTID=NULL)
MAPID NULLIF (MAPID=NULL)
CREATETIME DATE MM/DD/YYYY HH:MI:SS NULLIF (CREATETIME=NULL)
LASTMODIFIEDTIME DATE MM/DD/YYYY HH:MI:SS NULLIF (LASTMODIFIEDTIME=NULL)
)
BEGINDATA
;JP;Japan;;;// ::;NULL
;CN;China;;;// ::;NULL
;IN;India;;;// ::;NULL
;AU;Australia;;;// ::;NULL
;CA;Canada;;;// ::;NULL
;US;United States;;;// ::;NULL
;MX;Mexico;;;// ::;NULL
;GB;United Kingdom;;;// ::;NULL
;DE;Germany;;;// ::;NULL
;FR;France;;;// ::;NULL
;IT;Italy;;;// ::;NULL
;ES;Spain;;;// ::;NULL
;FI;Finland;;;// ::;NULL
;SE;Sweden;;;// ::;NULL
;IE;Ireland;;;// ::;NULL
;NL;Netherlands;;;// ::;NULL
;DK;Denmark;;;// ::;NULL
;BR;Brazil;;;// ::;NULL
;KR;Korea Republic of;;;// ::;NULL
;NZ;New Zealand;;;// ::;NULL
;BE;Belgium;;;// ::;NULL
;AT;Austria;;;// ::;NULL
;NO;Norway;;;// ::;NULL
;LU;Luxembourg;;;// ::;NULL
;PT;Portugal;;;// ::;NULL
;GR;Greece;;;// ::;NULL
;IL;Israel;;;// ::;NULL
;CH;Switzerland;;;// ::;NULL
;A;Anonymous Proxy;;;// ::;NULL
;A;Satellite Provider;;;// ::;NULL
;AD;Andorra;;;// ::;NULL
;AE;United Arab Emirates;;;// ::;NULL
;AF;Afghanistan;;;// ::;NULL
;AG;Antigua and Barbuda;;;// ::;NULL
;AI;Anguilla;;;// ::;NULL
;AL;Albania;;;// ::;NULL
;AM;armenia;;;// ::;NULL
;AN;Netherlands Antilles;;;// ::;NULL
;AO;Angola;;;// ::;NULL
;AP;Asia/Pacific Region;;;// ::;NULL
;AQ;Antarctica;;;// ::;NULL
;AR;Argentina;;;// ::;NULL
;AS;American Samoa;;;// ::;NULL
;AW;Aruba;;;// ::;NULL
;AZ;Azerbaijan;;;// ::;NULL
;BA;Bosnia and Herzegovina;;;// ::;NULL
;BB;Barbados;;;// ::;NULL
;BD;Bangladesh;;;// ::;NULL
;BF;Burkina Faso;;;// ::;NULL
;BG;Bulgaria;;;// ::;NULL
;BH;Bahrain;;;// ::;NULL
;BI;Burundi;;;// ::;NULL
;BJ;Benin;;;// ::;NULL
;BM;Bermuda;;;// ::;NULL
;BN;Brunei Darussalam;;;// ::;NULL
;BO;Bolivia;;;// ::;NULL
;BS;Bahamas;;;// ::;NULL
;BT;Bhutan;;;// ::;NULL
;BV;Bouvet Island;;;// ::;NULL
;BW;Botswana;;;// ::;NULL
;BY;Belarus;;;// ::;NULL
執行導入命令
C:\>sqlldr userid=system/manager control=testctl

   

   

   

   

  part ii


SQL*Loader是Oracle數據庫導入外部數據的一個工具它和DB的Load工具相似但有更多的選擇它支持變化的加載模式可選的加載及多表加載
  如何使用 SQL*Loader 工具
  我們可以用Oracle的sqlldr工具來導入數據例如:
  sqlldr scott/tiger control=loaderctl
  控制文件(loaderctl) 將加載一個外部數據文件(含分隔符) loaderctl如下:
  load data
  infile c:\data\mydatacsv
  into table emp
  fields terminated by optionally enclosed by
  ( empno empname sal deptno )
  mydatacsv 如下:
  Scott Tiger
  Frank Naude
  下面是一個指定記錄長度的示例控制文件* 代表數據文件與此文件同名即在後面使用BEGINDATA段來標識數據
  load data
  infile *
  replace
  into table departments
  ( dept position (:) char()
  deptname position (:) char()
  )
  begindata
  COSC COMPUTER SCIENCE
  ENGL ENGLISH LITERATURE
  MATH MATHEMATICS
  POLY POLITICAL SCIENCE
  Unloader這樣的工具
  Oracle 沒有提供將數據導出到一個文件的工具但是我們可以用SQL*Plus的select 及 format 數據來輸出到一個文件
  set echo off newpage space pagesize feed off head off trimspool on
  spool oradatatxt
  select col || || col || || col
  from tab
  where col = XYZ;
  spool off
  另外也可以使用使用 UTL_FILE PL/SQL 包處理:
  rem Remember to update initSIDora utl_file_dir=c:\oradata parameter
  declare
  fp utl_filefile_type;
  begin
  fp := utl_filefopen(c:\oradatatabtxtw);
  utl_fileputf(fp %s %s\n TextField );
  utl_filefclose(fp);
  end;
  /
  當然你也可以使用第三方工具如SQLWays TOAD for Quest等
  加載可變長度或指定長度的記錄
  如
  LOAD DATA
  INFILE *
  INTO TABLE load_delimited_data
  FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY
  TRAILING NULLCOLS
  ( data
  data
  )
  BEGINDATA
  AAAAAAAAAA
  ABCD
  下面是導入固定位置(固定長度)數據示例
  LOAD DATA
  INFILE *
  INTO TABLE load_positional_data
  ( data POSITION(:)
  data POSITION(:)
  )
  BEGINDATA
  AAAAAAAAAA
  BBBBBBBBBB
  跳過數據行
  可以用 SKIP n 關鍵字來指定導入時可以跳過多少行數據
  LOAD DATA
  INFILE *
  INTO TABLE load_positional_data
  SKIP
  ( data POSITION(:)
  data POSITION(:)
  )
  BEGINDATA
  AAAAAAAAAA
  BBBBBBBBBB
  導入數據時修改數據
  在導入數據到數據庫時可以修改數據注意這僅適合於常規導入並不適合 direct導入方式
  LOAD DATA
  INFILE *
  INTO TABLE modified_data
  ( rec_no my_db_sequencenextval
  region CONSTANT
  time_loaded to_char(SYSDATE HH:MI)
  data POSITION(:) :data/
  data POSITION(:) upper(:data)
  data POSITION(:)to_date(:data YYMMDD)
  )
  BEGINDATA
  AAAAAAAAAA
  BBBBBBBBBB
  LOAD DATA
  INFILE mail_orderstxt
  BADFILE bad_orderstxt
  APPEND
  INTO TABLE mailing_list
  FIELDS TERMINATED BY
  ( addr
  city
  state
  zipcode
  mailing_addr decode(:mailing_addr null :addr :mailing_addr)
  mailing_city decode(:mailing_city null :city :mailing_city)
  mailing_state
  )
  將數據導入多個表
  如:
  LOAD DATA
  INFILE *
  REPLACE
  INTO TABLE emp
  WHEN empno !=
  ( empno POSITION(:) INTEGER EXTERNAL
  ename POSITION(:) CHAR
  deptno POSITION(:) CHAR
  mgr POSITION(:) INTEGER EXTERNAL
  )
  INTO TABLE proj
  WHEN projno !=
  ( projno POSITION(:) INTEGER EXTERNAL
  empno POSITION(:) INTEGER EXTERNAL
  )
  導入選定的記錄
  如下例 () 代表第一個字符 (:) 代表之間的字符:
  LOAD DATA
  INFILE mydatadat BADFILE mydatabad DISCARDFILE mydatadis
  APPEND
  INTO TABLE my_selective_table
  WHEN () <> H and () <> T and (:) =
  (
  region CONSTANT
  service_key POSITION(:) INTEGER EXTERNAL
  call_b_no POSITION(:) CHAR
  )
  導入時跳過某些字段
  可用 POSTION(x:y) 來分隔數據 在Oraclei中可以通過指定 FILLER 字段實現FILLER 字段用來跳過忽略導入數據文件中的字段
  LOAD DATA
  TRUNCATE INTO TABLE T
  FIELDS TERMINATED BY
  ( field
  field FILLER
  field
  )
  導入多行記錄
  可以使用下面兩個選項之一來實現將多行數據導入為一個記錄:
  CONCATENATE: use when SQL*Loader should combine the same number of physical records together to form one logical record
  CONTINUEIF use if a condition indicates that multiple records should be treated as one Eg by having a # character in column
  SQL*Loader 數據的提交
  一般情況下是在導入數據文件數據後提交的
  也可以通過指定 ROWS= 參數來指定每次提交記錄數
  提高 SQL*Loader 的性能
  ) 一個簡單而容易忽略的問題是沒有對導入的表使用任何索引和/或約束(主鍵)如果這樣做甚至在使用ROWS=參數時會很明顯降低數據庫導入性能
  ) 可以添加 DIRECT=TRUE來提高導入數據的性能當然在很多情況下不能使用此參數
  ) 通過指定 UNRECOVERABLE選項可以關閉數據庫的日志這個選項只能和 direct 一起使用
  ) 可以同時運行多個導入任務
  常規導入與direct導入方式的區別
  常規導入可以通過使用 INSERT語句來導入數據Direct導入可以跳過數據庫的相關邏輯(DIRECT=TRUE)而直接將數據導入到數據文件中


From:http://tw.wingwit.com/Article/program/Oracle/201311/17401.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.