사이베이스 datetime -> 오라클로 이관 하기
날짜 타입의 경우 형식이 달라 바로 이관이 되지 않는다.
sql loader 로 들어갈수 있게 형식을 맞추고 집어넣어야 한다.
1. sybase 에서 view 만들기
-- 오라클 date 타입 으로 변경
create view aaa
as
select aa, bb,
convert(varchar, datetime_column, 112) || str_replace( convert(varchar, datetime_column, 8),':',null) dt
from souce_tab
go
=> yyyymmddhh24miss 형식으로 datetime 을 바꿔준다.
-- 오라클 timestamp 타입(milsec 까지) 으로 변경
convert(varchar, datetime_column, 112) || str_replace( convert(varchar, datetime_column, 20),':',null) dt
=> yyyymmddhh24missff 형식으로 datetime 을 바꿔준다.
2. bcp out 으로 덤프 내리기
bcp owner..table_name out ./dat/TAB_NM.dat -c -t'|~#' -r'!@\r\n' -U계정 -P비번
-t : field terminator => '|' 한글자는 데이터에 포함될 가능성이 있고, '||' 두글자는 널 컬럼이 많을 경우 제대로 인식 못할 가능성. 안전하게 특수문자 2,3 개
-r : row terminator => 필드 하나의 값에 여러줄 데이터가 있을 경우 loader 가 row 를 잘 인식못한다.
'특수문자 + 개행' 조합 으로 한줄의 끝을 정하자.
SELECT 'BCP '||OWNER||'..'||TABLE_NAME||' OUT ./dat/'||table_name||'.dat -c -t'||''''||'|~#'||''''||' -r'||''''||'!@\r\n'||''''||' -Uxx -Pp'
FROM DBA_TABLES
WHERE OWNER='XXX'
ORDER BY TABLE_NAME
;
3. control 파일 만들기
이관 대상 테이블이 여러개일 경우, 한방 쿼리로 ctl 파일을 만들자.
========= mk_ctl.sql ===========
SET PAGESIZE 0
SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
spool ./ctl/&&..ctl
SELECT 'LOAD DATA'||CHAR(10)
||'INFILE '||''''||'./dat/'||t.table_name||'.dat'||''''||' "str '||''''||'!@\r\n'||''''||'"'||CHR(10)
||'TRUNCATE '||CHR(10)
||'INTO TABLE '||OWNER||'.'||TABLE_NAME||CHR(10)
||'FIELDS TERMINATED BY '||''''||','||''''||CHR(10)
||'TRAILING NULLCOLS'||CHR(10)
||'('
FROM DBA_TABLES T
WHERE T.OWNER='AA'
AND T.TABLE_NAME LIKE '&&1'
UNION ALL
SELECT DECODE(COLUMN_ID,1,'',',')||COLUMN_NAME||
DECODE(DATA_TYPE,'VARCHAR2',' CHAR('||DATA_LENGTH||')',
'CHAR',' CHAR('||DATA_LENGTH||')',
'CLOB',' CHAR('||DATA_LENGTH||')',
'DATE',' DATE '||''''||'YYYYMMDDHH24MISS'||''''
)
FROM (
SELECT T.OWNER, T.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE,
C.DATA_LENGTH, C.COLUMN_ID
FROM DBA_TABLES T, DBA_TAB_COLS C
WHERE T.OWNER='AA'
AND T.TABLE_NAME = C.TABLE_NAME
AND T.TABLE_NAME LIKE '&&1'
ORDER BY COLUMN_ID
)
UNION ALL
SELECT ')' FROM DUAL
;
spool off
exit;
** data_type = timestamp(6) => .ctl timestamp missff
입력값 (테이블 이름) 을 받고 ctl 파일 만들기
========= mk_ctl.sh ===========
sqlplus -s lee/pwd @mk_ctl $1
========= run_mkctl.sh ===========
./mk_ctl.sh TAB_NM
./mk_ctl.sh TAB_NM
4. sql loader 실행
loader 스크립트 만들기
sqlldr userid=xxx/aaa control='./ctl/aaa.ctl' log='./log/aaa.log' bad='./bad/aaa.bad'
select 'sqlldr ....'
from dba_tables t
where t.owner='XXX'
order by table_name
