程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> Oracle數據庫備份與恢復(2):SQL*Loader

Oracle數據庫備份與恢復(2):SQL*Loader

編輯:Oracle數據庫基礎

一、基本知識

Oracle 的 SQL* LOADER 可以將外部格式化的文本數據加載到數據庫表中。通常 與 SPOOL導出文本數據方法配合使用。

1、命令格式

SQLLDR keyword=value [,keyWord=value,……]

例:$ sqlldr user/pwd control=emp.ctl data=emp.dat bad=emp.bad log=emp.log

2、控制文件

SQL*LOADER 根據控制文件可以找到需要加載的數據。並且分析和解釋這些數據。

控制文件由三個部分組成,具體參數參考幫助文檔:1. 全局選件,行,跳過的記錄數等;2. INFILE 子句指定的輸入數據;3. 數據特性說明。

comment: ——注釋

例:

load data infile *

append ——除了 append外,還有 insert、replace、truncate等方式

into table emp fIElds terminated b y ‘|’

no float external, name char(20),

age integer external,

duty char(1),salary float external,

upd_ts date(14) ‘YYYYMMDDHH24MISS’)

begindata

100000000003|Mulder|000020|1|000000005000|20020101000000

100000000004|Scully|000025|2|000000008000|20020101235959

控制文件中infile選項跟sqlldr 命令行中data 選項含義相同,如使用infile *則表明數據在本控制文件以 begin data 開頭的區域內。 一些選項:FIELDS TERMINATED BY WHITESPACE FIELDS TERMINATED BY x'09' FILLER_1 FILLER, // 指定某一列將不會被裝載

DEPTNO position(1:2), DNAME position(*:16), // 指定列的位置SEQNO RECNUM //載入每行的行號

SKIP n // 指定導入時可以跳過多少行數據

3、數據文件

按控制文件數據格式定義的數據行集,

例:

100000000001|Tom|000020|1|000000005000|20020101000000

100000000002|Jerry|000025|2|000000008000|20020101235959

固定格式、可變格式、流記錄格式:

固定格式:

當數據固定的格式(長度一樣)時且是在文件中得到時,要用 INFILE "fix n"

load data

infile 'example.dat' "fix 11"

into table example

fIElds terminated b y ',' optionally enclosed by '"'

(col1 char(5), col2 char(7)) example.dat:

001, cd, 0002,fghi,

00003,lmn,

1, "pqrs",

0005,uvwx,

可變格式:

當數據是可變格式(長度不一樣)時且是在文件中得到時,要用 INFILE "var n".如:

load data

infile 'example.dat' "var 3"

into table example

fIElds terminated b y ',' optionally enclosed by '"'

(col1 char(5), col2 char(7)) example.dat:

009hello,cd,010world,im,

012my,name is,

流記錄格式: // Stream-recored format:load data infile 'xx.dat' "str '|\n'"

into table xx fIEld terminated b y ',' optionally enclosed by '"'

(col1 char(5), col2 char(7))

example.dat:

hello, ccd,|

world, bb,|

4、壞文件

bad=emp.bad壞文件包含那些被 SQL*Loader拒絕的記錄。被拒絕的記錄可能是不符合要求的記錄。

5、日志文件及日志信息

log=emp.log當 SQL*Loader 開始執行後,它就自動建立 日志文件。日志文件包含有加載的總 結,加載中的錯誤信息等。

二、高級選項

1、Conventional Path Load與Direct Path Load

Conventional-path Load:通過常規通道方式上載。

特點:commit, always gen redo logs, enforce all constraints, fire insert triggers, can load into cluster, other user can make change

rows:每次提交的記錄數

bindsize:每次提交記錄的緩沖區

readsize:與 bindsize 成對使用,其中較小者會自動調整到較大者

sqlldr 先計算單條記錄長度,乘以 rows,如小於 bindsize,不會試圖擴張,rows以填充 bindsize;如超出,則以 bindsize 為准。 命令為:

$ sqlldr dbuser/Oracle control=emp.ctl log=emp.log rows=10000 bindsize=8192000

Direct-Path Load:

通過直通方式上載,可以跳過數據庫的相關邏輯,不進行 SQL解析,而直接將數 據導入到數據文件中。

特點:save, conditionly gen redo logs, enforce PK UK NN, not fire triggers, can not load into cluster, other user can not make change命令為:

$ sqlldr dbuser/Oracle control=emp.ctl log=emp.log direct=true

2、SPOOL導出文本數據方法

導入的數據文件可以用 SPOOL導出文本數據方法生成。

SQL*PLUS環境設置

SET NEWPAGE NONE HEADING OFF SPACE 0

PAGESIZE 0 SET TRIMOUT ON TRIMSPOOL ON LINESIZE 2500

注:LINESIZE 要稍微設置大些,免得數據被截斷,它應和相應的 TRIMSPOOL結合使用防止導出的文本有太多的尾部空格。

但是如果 LINESIZE 設置太大,會大大降低導出的速度,另外在 Windows下導 出最好不要用 PLSQL導出,速度比較慢,直接用 COMMEND 下的 SQLPLUS命令最 小化窗口執行。對於字段內包含很多回車換行符的應該給與過濾,形成比較規矩的文本 文件。

通常情況下,我們使用 SPOOL方法,將數據庫中的表導出為文本文件,如下述:

set trimspool on

set linesize 120 pagesize 2000 newpage 1 heading off term off spool 路徑+文件名

select col1||','||col2||','||col3||','||col4||'……' from tablename;

spool off

三、腳本

1、將表中數據記錄導出為字段值用分隔符'|'分開的。dat文件

#!/bin/ksh

##################################################################

## 名稱: unloadtable

## 功能: 本 shell 用於將表中數據記錄導出

## 導出為字段值用分隔符'|'分開的。dat文件

## 編者:

## 日期: 2006.03.18

##################################################################

if [ $# -ne 3 ]

then echo "usage:unloadtable tablename username passWord."

exit 0

fi

##准備工作

echo "set heading off " >/tmp/$1.col

echo "set pagesize 0" >>/tmp/$1.col

echo "set linesize 800 " >>/tmp/$1.col

echo "set feedback off " >>/tmp/$1.col

echo "set tab off " >>/tmp/$1.col

echo "select column_name||',' from user_tab_columns where lower(table_name)='$1' order by

column_id; " >> /tmp/$1.col

##產生 select 語句

echo "set heading off " >/tmp/$1.sel

echo "set pagesize 0" >>/tmp/$1.sel

echo "set linesize 800 " >>/tmp/$1.sel

echo "set feedback off " >>/tmp/$1.sel

echo "set tab off " >>/tmp/$1.sel

echo "select " >>/tmp/$1.sel

echo `sqlplus -s $2/$3 < /tmp/$1.col` |sed "s/,/||'|'||/g" |sed "s/||$//g"|sed "s/date/\"date\"/g"

>>/tmp/$1.sel

##生成 dat文件

#echo "from $1;\n/" >>/tmp/$1.sel 由於 / 導致多執行一次 select

echo "from $1;\n" >>/tmp/$1.sel

sqlplus -s $2/$3 < /tmp/$1.sel >$1_tmp.dat

#awk '{if(FNR!=1) print $0}' $1_tmp.dat >$1.dat FNR 選項使得第一條記錄選不出

awk '{print $0}' $1_tmp.dat >$1.dat

rm -f $1_tmp.dat

2、將數據導入到相應表中

#!/bin/ksh

##################################################################

## 名稱:loadtable

## 功能:本 shell 用於將已經准備好的。dat數據文件導入相應的表中

## .dat 文件各個字段值用分隔符'|'分開。

## 編者:

## 日期: 2006.03.18

##################################################################

if [ $# -ne 3 ]

then

echo "usage:loadtable tablename username passWord." exit 0 fi

##准備工作

echo "set heading off " >/tmp/$1.colsql

echo "set pagesize 0" >>/tmp/$1.colsql

echo "set linesize 800 " >>/tmp/$1.colsql

echo "set feedback off " >>/tmp/$1.colsql

echo "set tab off " >>/tmp/$1.colsql

echo "select column_name||',' from user_tab_columns where lower(table_name)='$1' order by

column_id; " >> /tmp/$1.colsql

##產生 ctl文件

echo "load data" >/tmp/$1.ctl

echo "infile *" >>/tmp/$1.ctl

echo "into table $1" >>/tmp/$1.ctl

echo "fIElds terminated by '|'" >>/tmp/$1.ctl

echo `sqlplus -s $2/$3 < /tmp/$1.colsql` |sed "s/,$/)/g" |sed "s/^/(/g" >>/tmp/$1.ctl

##開始導入數據

echo "truncate table $1;" >/tmp/$1.sql

sqlplus $2/$3 < /tmp/$1.sql

sqlldr $2/$3 data=$1.dat control=/tmp/$1.ctl log=/tmp/$1.log

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved