데이터를 export 하여 가령 다른 서버로 전송한 후 sqlloader로 import할 때
특히 네트워크 속도가 좋지 않은 경우
덤프파일을 압축하면 전체적으로 시간을 절감할 수 있다.
데이터를 export할 공간이 넉넉하지 않은 경우에도 도움이 될 수 있다.
데이터 export 수행할 쿼리 파일을 생성한다.
-- in export.sql
SET term on
SET echo off
SET UNDERLINE off
SET HEADSEP off
SET TRIMSPOOL on
SET TRIMOUT on
SET COLSEP ","
SET SQLPROMPT ''
SET PAGESIZE 0
SET LINESIZE 32767
SET LONG 999999999
SET TERMOUT OFF
SET FEEDBACK OFF
SPOOL export.pipe;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SELECT
col1
||'@@'|| col2
||'@@'|| col3
||'@@'|| col4
||'@@'|| col5
FROM TBL WHERE 1=1;
SPOOL OFF;
quit;
/
맨 마지막에 quit; / 부분이 없으면, 쿼리를 수행한 후 sqlplus가 종료되지 않고 멍하니 멈춰있게 되므로 유의한다.
sqplus로 데이터 export → pipe 파일 → gzip 으로 direct하여 export와 동시에 압축되도록 한다.
rm -f ./export.pipe
mkfifo ./export.pipe
chmod 770 ./export.pipe
cat ./export.pipe | gzip > ./export.csv.gz &
sqlplus -s ${DBUSER}/${password} @export.sql
rm -f ./export.pipe
반대로, 이 파일을 sqlload할 때는 다음과 같이 수행한다.
LOAD DATA
INFILE import.pipe
BADFILE import.bad
APPEND INTO TABLE TBL
FIELDS TERMINATED BY "@@"
TRAILING NULLCOLS
(
col1
,col2
,col3
,col4 CHAR -- load하는 타겟 테이블의 칼럼이 VARCHAR2 칼럼일 경우 굳이 명시하자면
,col5 INTEGER -- load하는 타겟 테이블의 칼럼이 NUMBER일 경우
)
rm -f ./import.pipe
mkfifo ./import.pipe
chmod 770 ./import.pipe
gunzip < ./export.csv.gz > ./import.pipe &
sqlldr userid=${DBUSER}/${password} control=import.ctl errors=100 rows=5000
rm -f ./import.pipe
INFILE을 지정할 때 현재 디렉토리임을 명시하려고 ./import.pipe로 쓰면 오류가 발생하므로
그냥 파일이름만 써줘야 한다.
마찬가지로 sqlldr 커맨드에서 control 파일을 control=./import.ctl 로 지정하면 오류 발생한다.
컨트롤 파일을 통해 여러 옵션을 조정할 필요가 없다면
간단히 sqlldr userid=${DBUSER}/${password} data=import.pipe 로 수행할 수도 있다.
5000건 단위로 commit을 위해 rows=5000을 지정했지만, 실제 수행될 때는 5천 건 단위로 커밋을 찍지 않는데, 이런 경우에는 bindsize 옵션을 추가해볼 수 있다.
