A. oraclesqlloader全攻略
一 sql loader 的特點oracle自己帶了很多的工具可以用來進行數據的遷移 備份和恢復等工作 但是每個工具都有自己的特點 比如說exp和imp可以對資料庫中的數據進行導出和導出的工作 是一種很好的資料庫備份和恢復的工具 因此主要用在資料庫的熱備份和恢復方面 有著速度快 使用簡單 快捷的優點 同時也有一些缺點 比如在不同版本資料庫之間的導出 導入的過程之中 總會出現這樣或者那樣的問題 這個也許是oracle公司自己產品的兼容性的問題吧 sql loader 工具卻沒有這方面的問題 它可以把一些以文本格式存放的數據順利的導入到oracle資料庫中 是一種在不同資料庫之間進行數據遷移的非常方便而且通用的工具 缺點就速度比較慢 另外對blob等類型的數據就有點麻煩了 二 sql loader 的幫助C:>sqlldr
SQL*Loader: Release Proction on 星期六 月 : :
Copyright (c) Oracle Corporation All rights reserved
用法: SQLLDR keyword=value [ keyword=value ]
有效的關鍵字:
userid ORACLE username/passwordcontrol Control file namelog Log file namebad Bad file namedata Data file namediscard Discard file namediscardmax 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 beeen direct path data saves（默認: 常規路徑 所有直接路徑）bindsize Size of conventional path bind array in bytes(默認 )silent Suppress messages ring run (header feedback errors discards partitions)direct use direct path (默認FALSE)parfile parameter file: name of file that contains parameter specificationsparallel do parallel load (默認FALSE)file File to allocate extents fromskip_unusable_indexes disallow/allow unusable indexes or index partitions(默認FALSE)skip_index_maintenance do not maintain indexes mark affected indexes as unusable(默認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 pathresumable enable or disable resumable for current session(默認FALSE)resumable_name text string to help identify resumable statementresumable_timeout wait time (in seconds) for RESUMABLE(默認 )date_cache size (in entries) of date conversion cache(默認 )
PLEASE NOTE: 命令行參數可以由位置或關鍵字指定 前者的例子是 sqlloadscott/tiger foo ; 後一種情況的一個示例是 sqlldr control=foouserid=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: 用記事本編輯 控制文件 input ctl 內容如下 load data 控制文件標識 infile test txt 要輸入的數據文件名為test txt append into table test 向表test中追加記錄 fields terminated by X 欄位終止於X 是一個製表符（TAB） (id username password sj) 定義列對應順序 a insert 為預設方式 在數據裝載開始時要求表為空 b append 在表中追加新記錄 c replace 刪除舊記錄 替換成新裝載的記錄 d truncate 同上 在DOS窗口下使用SQL*Loader命令實現數據的輸入 C:>sqlldr userid=system/manager control=input ctl 默認日誌文件名為 input log 默認壞記錄文件為 input bad 還有一種方法可以把EXCEL文件另存為CSV（逗號分隔）(* csv) 控制文件就改為用逗號分隔 LOAD DATA INFILE d:car csv APPEND INTO TABLE t_car_temp FIELDS TERMINATED BY (phoneno vip_car)
b）在控制文件中直接導入數據
控制文件test ctl的內容 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 DATAINFILE *BADFILE C:Documents and SettingsJackey桌面WMCOUNTRY BAD DISCARDFILE C:Documents and SettingsJackey桌面WMCOUNTRY DSC INSERT INTO TABLE EMCCOUNTRYFields 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 ; Azerjan ; ; ; / / : : ;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=test ctl
part ii
SQL*Loader是Oracle資料庫導入外部數據的一個工具 它和DB 的Load工具相似 但有更多的選擇 它支持變化的載入模式 可選的載入及多表載入 如何使用 SQL*Loader 工具我們可以用Oracle的sqlldr工具來導入數據 例如:sqlldr scott/tiger control=loader ctl控制文件(loader ctl) 將載入一個外部數據文件（含分隔符) loader ctl如下:load datainfile c:datamydata csv into table empfields terminated by optionally enclosed by ( empno empname sal deptno )mydata csv 如下: Scott Tiger Frank Naude 下面是一個指定記錄長度的示例控制文件 * 代表數據文件與此文件同名 即在後面使用BEGINDATA段來標識數據 load datainfile *replaceinto table departments( dept position ( : ) char( ) deptname position ( : ) char( ))begindataCOSC PUTER SCIENCEENGL ENGLISH LITERATUREMATH MATHEMATICSPOLY POLITICAL SCIENCEUnloader這樣的工具Oracle 沒有提供將數據導出到一個文件的工具 但是 我們可以用SQL*Plus的select 及 format 數據來輸出到一個文件 set echo off newpage space pagesize feed off head off trimspool onspool oradata txtselect col || || col || || col from tab where col = XYZ ;spool off另外 也可以使用使用 UTL_FILE PL/SQL 包處理:rem Remember to update initSID ora utl_file_dir= c:oradata parameterdeclarefp utl_file file_type;beginfp := utl_file fopen( c:oradata tab txt w );utl_file putf(fp %s %s TextField );utl_file fclose(fp);end;/當然你也可以使用第三方工具 如SQLWays TOAD for Quest等 載入可變長度或指定長度的記錄如 LOAD DATAINFILE *INTO TABLE load_delimited_dataFIELDS TERMINATED BY OPTIONALLY ENCLOSED BY TRAILING NULLCOLS( data data )BEGINDATA AAAAAAAAAA A B C D 下面是導入固定位置（固定長度）數據示例 LOAD DATAINFILE *INTO TABLE load_positional_data( data POSITION( : ) data POSITION( : ))BEGINDATA AAAAAAAAAA BBBBBBBBBB跳過數據行 可以用 SKIP n 關鍵字來指定導入時可以跳過多少行數據 如 LOAD DATAINFILE *INTO TABLE load_positional_dataSKIP ( data POSITION( : ) data POSITION( : ))BEGINDATA AAAAAAAAAA BBBBBBBBBB導入數據時修改數據 在導入數據到資料庫時 可以修改數據 注意 這僅適合於常規導入 並不適合 direct導入方式 如 LOAD DATAINFILE *INTO TABLE modified_data( rec_no my_db_sequence nextval 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 DATAINFILE mail_orders txt BADFILE bad_orders txt APPENDINTO TABLE mailing_listFIELDS 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 DATAINFILE *REPLACEINTO TABLE empWHEN empno != ( empno POSITION( : ) INTEGER EXTERNAL ename POSITION( : ) CHAR deptno POSITION( : ) CHAR mgr POSITION( : ) INTEGER EXTERNAL)INTO TABLE projWHEN projno != ( projno POSITION( : ) INTEGER EXTERNAL empno POSITION( : ) INTEGER EXTERNAL)導入選定的記錄 如下例 ( ) 代表第一個字元 ( : ) 代表 到 之間的字元:LOAD DATAINFILE mydata dat BADFILE mydata bad DISCARDFILE mydata dis APPENDINTO TABLE my_selective_tableWHEN ( ) <> H and ( ) <> T and ( : ) = (region CONSTANT service_key POSITION( : ) INTEGER EXTERNAL call_b_no POSITION( : ) CHAR)導入時跳過某些欄位 可用 POSTION(x:y) 來分隔數據 在Oracle i中可以通過指定 FILLER 欄位實現 FILLER 欄位用來跳過 忽略導入數據文件中的欄位 如 LOAD DATATRUNCATE INTO TABLE T FIELDS TERMINATED BY ( field field FILLER field )導入多行記錄 可以使用下面兩個選項之一來實現將多行數據導入為一個記錄:CONCATENATE: use when SQL*Loader should bine 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) 而直接將數據導入到數據文件中 lishixin/Article/program/Oracle/201311/17401
B. rman怎麼恢復控制文件
如果有全部數據文件是可以重建的ctl的，腳本還是很容易寫得，找一個好的db來alter database backup controlfile to ''然後對著修改回就行了，不過你必須清答楚完整的數據文件的位置。如果沒有控制文件的備份，一般不建議重建ctl，有備份的ctl最好，是oracle 9i還是10g的是有控制文件的快照，特殊情況下可以採取快照來恢復。