操作環境
客戶端 Oracle 10g
服務端 Oracle 11g
1、數據準備
1.1、在sqlplus下將相關表將相關待導入數據導出為txt文件
--sqlplus腳本
Set linesize 3000 --每行長3000
Set pagesize 0 --指定不分頁
Set heading off --不輸出標題行
Set feedback off --不輸出反饋信息
Set echo off --不顯示命令本身
Set termout off --不顯示終端信息
Set trimout On --截斷終端顯示空格
Set trimspool On --截斷輸出至日志空格
spool c:\a.txt --指定屏幕輸出到相關文件
spool off --關閉指定輸出
以上腳本控制sqlplus控制臺輸出打印效果, 同時指定屏幕的打印輸出將轉錄入到何文件中
實際腳本
sqlplus zhsj/zhsj@zhsj
Set linesize 3000
Set pagesize 0
Set heading off
Set feedback off
Set echo off
Set termout off
Set trimout On
Set trimspool On
spool c:\a.txt
select employee_id||'|'||employee_name||'|'||employee_salary from t_employee;
spool off
以上會將t_employee表中的 employee_id、employee_name、employee_salary 三列數據以如下形式寫入c:\a.txt 文件中
1|znp|80000.00
2|zhsj|40000.00
3|xxx|30000.00
數據準備完成后編寫sqlldr要用到的ctl文件，用于將txt文件中的數據導入到數據庫中，腳本如下 （--部分實際腳本中須刪險)
-- a.ctl
Load data --加載數據
infile a.txt --加載數據文件名稱(現在是相對路徑,可以寫成絕對路徑c:\bak.txt)
badfile a.bad --失敗數據寫入文件
append -- append 追加 truncate truncate delete 刪除
into table t_employee_bak --插入操作表
FIELDS TERMINATED BY '|' --根據指定標識符隔斷各字段值 OPTIONALLY ENCLOSED BY '"' 過濾掉指定的標識符,如"等
(employee_id, --指定插入字段及數據
employee_name,
employee_salary)
準備完成后，在cmd下輸入
sqlldr -userid zhsj/zhsj@zhsj control=c:\a.ctl
執行，在t_employee_bak表中可以見到a.txt中的數據已導入
在執行過程如果出現數據沒有導入的情況，可能原因會有以下幾種情況
1、在導出的數據文件中存在同時打印輸出的命令行，將命令行刪除后重新保存文件即可將數據正常插入到數據庫中
2、雖然文本中已是正確的數據但仍然無法導入,此時在執行的SQL腳本中增加一個間隔符的輸出，即
select employee_id||'|'||employee_name||'|'||employee_salary from t_employee;
改為
select employee_id||'|'||employee_name||'|'||employee_salary||'|' from t_employee;
此時將會正常導入，這是因為列值存在空數據導致間隔計算問題
以上為txt格式數據文件導出
對于異質數據庫，可以采用sqldeveloper等工具將相關數據庫中數據以csv格式導出，此時在編寫執行腳本時須變更如下
-- a.ctl
Load data --加載數據
infile a.txt --加載數據文件名稱(現在是相對路徑,可以寫成絕對路徑c:\bak.txt)
badfile a.bad --失敗數據寫入文件
append -- append 追加 truncate truncate delete 刪除
into table t_employee_bak --插入操作表
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' --根據指定標識符隔斷各字段值 OPTIONALLY ENCLOSED BY '"' 過濾掉指定的標識符,如"等
(employee_id, --指定插入字段及數據
employee_name,
employee_salary)
因為csv格式文件在用txt打開可以看到數據文件格式為"1","znp","80000.00"，需要將' " '標識符去掉，才可以正常導入
大數據量時csv格式文件會比txt格式文件大3倍左右，且導入時可能出現問題，所以盡量采用txt文件方式導入