批處理sqlldr資料移轉--oracle txt匯入匯出

標籤：

sqlldr資料移轉--oracle txt匯入匯出

http://coupling001.blog.163.com/blog/static/174925389201262093959635/

 

一、sqlldr匯入txt

1.預備

a).txt檔案

這裡要儲存成無簽名的UTF-8

b).oracle建表

2.編寫控制檔案input_test.ctl

LOAD  DATA   

CHARACTERSET ‘UTF8‘    --字元集設定 
INFILE ‘d:\input_test.txt‘      --要匯入的文本資料路徑,可寫多個
REPLACE into TABLE input_test  --清空原有資料再匯入方式   追加匯入 用append into table t_name
fields terminated by X‘09‘   --以定位字元分隔
trailing nullcols                   --允許空列匯入

(col1,col2)

注:

infile ‘d:\input_test.txt‘表示需要裝載的資料檔案的路徑

append into table test 資料載入的表:
(1)append 表示表中有資料，加在後面
(2)INSERT 表示裝入空表，有資料則停止。預設值
(3)REPLACE 原先表中如果有資料，會被刪除
(4)TRUNCATE 如果要載入的資料與現在的資料相同，載入的資料替換現存的資料。
fields terminated by ‘,‘

表示資料用是‘,‘分隔的,用by X‘09‘,即16進位的"09"代表TAB定位字元,常用於excel轉換的tab定位字元檔案的資料的匯入。常用分隔字元還有‘|‘

多語種可設定字元集編碼為:CHARACTERSET ‘UTF8‘

3.DOS下執行

sqlldr system/[email protected], control=c:\input\input_test.ctl log=c:\input\input_test.log bad=c:\input\input_test.bad

有效關鍵字:

userid -- ORACLE username/password

control – 控制檔案

log – 記錄的記錄檔

bad – 壞資料檔案

data – 資料檔案

discard – 丟棄的資料檔案

discardmax – 允許丟棄資料的最大值 (全部預設)

skip -- Number of logical records to skip (預設0)

load -- Number of logical records to load (全部預設)

errors – 允許的錯誤記錄數 (預設50)

rows -- Number of rows in conventional path bind array or between direct path data saves（每次提交的記錄數，預設: 常規路徑 64, 所有直接路徑）

bindsize -- Size of conventional path bind array in bytes(預設256000)

每次提交記錄的緩衝區的大小(位元組為單位，預設256000)

silent --禁止輸出資訊 (header,feedback,errors,discards,partitions)

direct – 使用直通路徑方式匯入 (預設FALSE)

parfile -- parameter file: name of file that contains parameter specifications

parallel -- 並行匯入 (預設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 unusable(預設FALSE)

readsize -- Size of Read buffer (預設1048576)

與bindsize成對使用，其中較小者會自動調整到較大者。sqlldr先計算單條記錄長度，乘以rows，如小於bindsize，不會試圖擴張rows以填充bindsize；如超出，則以bindsize為準。

 

external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE(預設NOT_USED)

columnarrayrows -- Number of rows for direct path column array(預設5000)

streamsize -- Size of direct path stream buffer in bytes(預設256000)

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(預設7200)

date_cache -- size (in entries) of date conversion cache(預設1000)

 

4.寫成.bat批處理

上述3步已完成了txt匯入,在windows下還可將sqlldr命令寫成批次檔,雙擊執行.

@echo off

echo input_test   

pause     --暫停,建議加入，以免錯誤雙擊執行
@rem
sqlldr system/[email protected], control=c:\input\input_test.ctl log=c:\input\input_test.log bad=c:\input\input_test.bad
@rem
@rem sqlldr system/[email protected], control=c:\input\input_test.ctl rows=100000
pause

 

二、sqlldr匯出txt

利用spool 匯出txt

1.寫output.sql

sqlplus system/[email protected] as sysdba  --串連oracle
CHARACTERSET al32UTF8            --設定編碼集
set trimspool on                               --開啟池
spool c:\output\output.txt                  --池輸出路徑
set pagesize 0                                 --頁設定
set heading off                                 --關閉表頭
set linesize 32767                            --最大行顯
select ‘#‘||col1||‘#,#‘||col2||‘#,#‘||col3||‘#‘  --設定需要的列格式。此例，列間以以逗號分隔，列內容用#引起。

from test_data;
exit;                                                 --退出sqlplus
spool off                                          --關閉池
pause

註：上述命令在dos下直接敲命令執行亦可。

2.寫成.bat批處理

再寫bat調用上面的outputsql檔案，如下：

cd/
set NLS_LANG=.AL32UTF8    --設定字元集utf8
chcp 65001                             --轉換編碼頁utf8
@echo off
echo  data output
pause
@rem
 sqlplus system/[email protected] as sysdba @c:\dmp_sql\output.sql 
@rem 
pause

 

批處理sqlldr資料移轉--oracle txt匯入匯出（轉）