/*********************************************
* SAMPLE : SQL*LOADER SAMPLES PART I
*********************************************/
SQL*Loader 는 외부 화일의 데이타를 ORACLE 데이타베이스의 table에 넣기
위한 유틸리티입니다. SQL*Loader를 사용하려면 외부 데이타 화일과
콘트롤 화일이 필요합니다. 콘트롤 화일이라고 하는 것은 로드하는 데이타의
정보를 저장한 화일입니다. 간단한 샘플 콘트롤 화일을 설명하겠습니다.
INFILE sample.dat 외부 화일을 지정(경로,파일명)합니다.
REPLACE 테이블에 데이타 넣는 방법 지정
INTO TABLE table_name 데이타를 로드하는 테이블을 지정
FIELDS TERMINATED BY ',' 데이타 필드의 종결 문자 지정
(a integer external, 테이블의 열, 외부 데이타 화일의 데이타 형을 지정
b char)
APPEND 새로운 행을 기존의 데이타에 추가
INSERT 비어 있는 테이블에 넣을 때
TRUNCATE 테이블의 기존 데이타를 모두 TRUNCATE 하고 INSERT
* 데이타 에러 때문에 로드가 안된 레코드를 저장한 화일(확장자는 bad)
* 사용자의 선택 기준에 적합하지 않은 레코드를 저장한 화일(discard 화일)
이것은 discardfile 옵션으로 별도로 지정해야 생성됩니다.
CREATE TABLE cons_test
(a number,
b number,
c number,
d varchar(10))
LOAD DATA
INFILE cons.dat
REPLACE
INTO TABLE cons_test
FIELDS TERMINATED BY ','
(a integer external,
b integer external,
c CONSTANT '100',
d char)
1,2,DATA
2,4,DATA2
SQL>SELECT * FROM cons_test;
-------------------------
1 2 100 DATA
2 4 100 DATA2
그것으로 완결된 열 지정의 하나가 됩니다. integer external 데이타 형은
수치 데이타를 문자형식(ASCII CODE)로 나타낸 것입니다.
CREATE TABLE sysdatetb
(a number,
b date,
c varchar(10))
LOAD DATA
INFILE sysdate2.dat
REPLACE
INTO TABLE sysdatetb
FIELDS TERMAINATED BY ','
(a integer external,
b sysdate,
c char(10))
111,STRINGS
222,STRINGS2
SQL>SELECT * FROM sysdatetb;
---------------------------
111 13-MAY-94 STRING
222 13-MAY-94 STRING2
그것으로 완결된 열 지정의 하나가 됩니다. 새로운 시스템 날짜매김은 컨벤셔널
패스에서는 실행 시에 삽입된 각각의 레코드 배열마다 또, 다이렉트 패스의
경우는 로드된 각각의 레코드의 블럭마다 사용됩니다.
CREATE TABLE rectb
(a varchar(10),
b number,
c varchar(10))
LOAD DATA
INFILE rec.dat
REPLACE
INTO TABLE rectb
FIELDS TERMINATED BY ','
(a char,
b recnum,
c char)
A,a
B,b
C,c
SQL>SELECT * FROM rectb;
---------------------
A 1 a
B 2 b
C 3 c
그것으로 완전한 열 지정의 하나가 됩니다. 연속번호는 1부터 차례대로 1씩
더해져서 번호가 매겨집니다. 가산된 번호를 둘씩 건너뛰거나 하는 것은 불가능
합니다.
CREATE TABLE seqtb
(a varchar(10),
b number,
c varchar(10))
LOAD DATA
INFILE seq.dat
REPLACE
INTO TABLE seqtb
FIELDS TERMINATED BY ','
(a char,
b sequence(100,5),
c char)
1,a
2,b
3,c
SQL>SELECT * FROM seqtb;
-------------
1 100 a
2 105 b
3 110 c
그것으로 완결된 열 지정의 하나가 됩니다. 초기 값 100과 늘인 값 5는 다른
수치로 변경 가능합니다.
(물리 레코드의 1바이트 째로 판단되는 경우)
CREATE TABLE conti_test
(a varchar(10),
b varchar(10),
c varchar(10))
LOAD DATA
INFILE conti.dat
REPLACE
COUTINUEIF THIS
(1) = '%'
INTO TABLE conti_test
FIELDS TERMINATED BY ','
(a char,
b char,
c char)
%1,
%2,
3
%A,B
,C
%a,b
%c
%d
,ef
SQL>SELECT * FROM conti_test;
---------------------
1 2 3
A B C
a bcd ef
때문에, 실 데이타를 1 바이트 째부터 시작해서는 안됩니다. 상기 예의 경우,
레코드의 선두 바이트가 '%'일 때 다음의 레코드가 연결됩니다.
(구성하는 물리 레코드 수가 모두 일정한 경우)
CREATE TABLE con_test
(a varchar(10),
b varchar(10),
c varchar(10))
LOAD DATA
INFILE conti.dat
REPLACE
--일례로 모든 논리레코드가 그 레코드로 구성됩니다.
CONCATENATE 2
INTO TABLE con_test
FIELDS TERMINATED BY ','
(a char,
b char,
c char)
1,2,
3
a,b,
c
A,
B,C
SQl) SELECT * FROM con_test;
-------------------------
1 2 3
a b c
A B C
성립되는 것 같은 단순한 경우에 한합니다.
CREATE TABLE enc
(a varchar(10),
b varchar(10),
c varchar(10))
LOAD DATA
INFILE enc.dat
REPLACE
INTO TABLE enc
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ' " ' AND ' " '
(a char,
b char,
c char)
"abc,d",2,3
"a,,d",4,5
SQL>SELECT * FROM enc;
-------------------------
abc,d 2 3
a,,d 4 5
CREATE TABLE pretb
(a varchar(10),
b varchar(10),
c varchar(10))
LOAD DATA
INFILE pre.dat
REPLACE
PRESERVE BLANKS
INTO TABLE pretb
(a position(01:05) char,
b position(06:10) char,
c position(11:20) char)
12 4 67890 ab def hi
2 67890 ab def hi
SQL>SELECT * FROM pretb;
--------------------------
12 4 67890 ab def hi
2 67890 ab def hi
SQL>SELECT LENGTH(a), LENGTH(c) FROM pretb;
-------------------
5 10
5 10
넣도록 하고 싶다
CREATE TABLE tratb
(a varchar(10),
b varchar(10),
c varchar(10))
LOAD DATA
INFILE tra.dat
REPLACE
INTO TABLE tratb
FIELDS TERMINATED BY ','
trailing nullcols
(a char,
b char,
c char)
1,aa,
2,bb,FF
3,cc,
SQL>SELECT * FROM tratbl
------------------------
1 aa
2 bb FF
3 cc
데이타 에러가 됩니다. 데이타가 들어 있기도 하고 없기도 한 열의 데이타는
데이타 화일의 최후로 가져갑니다.
CREATE TABLE nulltb
(a varchar(10),
b varchar(10),
c varchar(10))
LOAD DATA
INFILE null.dat
REPLACE
INTO TABLE nulltb
FIELDS TERMINATED BY ','
(a char,
b char,
c char(10) nullif c = blanks)
aa,bb, ,
11,22, ,
99,88,AA
00,00,BB
SQL>SELECT * FROM nulltb;
-------------------------
aa bb
11 22
99 88 AA
00 00 BB
CREATE TABLE nulltb2
(a varchar(10),
b varchar(10),
c date)
LOAD DATA
INFILE null2.dat
REPLACE
INTO TABLE nulltb2
FIELDS TERMINATED BY ','
(a char,
b char,
c date "YY/MM/DD" nullif c = blanks)
aa,bb, ,
11,22, ,
99,88,92/11/11,
00,00,94/12/12,
SQL>SELECT * FROM nulltb2;
-------------------------
aa bb
11 22
99 88 92/11/11
00 00 94/12/12
1.12 POSITION 지정 시 BLANK를 그대로 로드하고 싶은 경우
CREATE TABLE nulltb2
(a varchar(10),
b varchar(10),
c date)
--- position 지정으로 블랭크를 그대로 입력 원하는 경우
--- preserve blanks를 지정한다.
LOAD DATA
INFILE null3.dat
REPLACE
PRESERVE blanks
INTO TABLE nulltb2
(a position(1:2) char,
b position(3:4) char nullif b = blanks,
c position(5:13) date "YY/MM/DD")
998892/11/11
94/12/12
SQL>select * from nulltb2;
-------------------------
99 88 92/11/11
94/12/12
-------------------
2 2
2
주의사항 : 이 경우 2 레코드 째는 A에 블랭크가 들어가고 B에 NULL이 들어갑니다.
CREATE TABLE def2
(a varchar(10),
b varchar(10),
c number)
LOAD DATA
INFILE def2.dat
REPLACE
INTO TABLE def2
FIELDS TERMINATED BY ','
(a char,
b char,
c integer external defaultif c = blanks)
11,11,123
22,22, ,
33,33, ,
44,44, ,
SQL>SELECT * FROM deft;
------------------------
11 11 123
22 22 0
33 33 0
44 44 0
CREATE TABLE ifnulltb
(a varchar(10),
b varchar(10),
c varchar(10))
LOAD DATA
INFILE ifnull.dat
REPLACE
INTO TABLE ifnulltb
FIELDS TERMINATED BY by ','
(a char,
b char "nvl(:b,'NULL')",
c char)
1,2,3,
A,,B
a,b,c
SQL>SELECT * FROM ifnulltb;
-------------------------
1 2 3
A NULL B
a b c
주의 사항 : NVL과 같은 SQL 함수는 DIRECT LOAD의 경우 SQL 인터페이스를
경유하지 않기 때문에 사용할 수 없습니다.
CREATE TABLE uptb
(a varchar(10),
b varchar(10))
LOAD DATA
INFILE upper.dat
REPLACE
INTO TABLE uptb
FIELDS TERMINATED BY ','
(a char "lower(:a)",
b char "upper(:b)")
aBcDeFg,AbCdEf
ccDD11,ffGG22
SQL>SELECT * FROM uptb;
--------------------
abcdefg ABCDEF
cdd11 FFGG22
1111 0001 1111 0010 1100 0011
10진 1 2 + 3
Hex C0 to C9 → +0 to +9(부호비트)
Hex D0 to D9 → +0 to -9(부호비트)
------------------------------------
부호 ++++++++++----------
수 01234567890123456789
create table z
(a varchar(10),
i number,
j number)
load data
infile filename.dat
replace
into table z
(a position (01) char,
i position(02:07) zoned(6),
j position(08:13) zoned(6))
A12345{12345}
SQL>select * from z;
-----------------------
A 123450 -123450
0001 0010 0011 1100
10진 1 2 3 +
create table dec
(a varchar(10),
col1 number,
col2 number)
load data
infile filename.dat
replace
into table dec
(a position (01:01) char,
col1 position(02:05) decimal(7,4),
col2 position(06:09) decimal(7,4))
데이타 화일은 바이너리이기 때문에 more 등으로 볼 수 없습니다.
SQL>select * from dec;
--------------------------------
A 123.4567 -123.4567
주의 사항 :
콘트롤 화일에서 decimal 형을 사용하는 것에 따라 (length, scale)을 지정합니다.
CREATE TABLE fixtb
(a varchar(10),
b varchar(10))
LOAD DATA
INFILE fix.dat
"FIX 6"
REPLACE
INTO TABLE fixtb
(a char(3),
b char(3))
123456abcdef
3132 3334 3536 6162 6364 6566
SQL>SELECT * FROM fixtb;
---------------
123 456
abc def
주의 사항 : 데이타 화일의 끝에는 OxOa(CARRIAGE RETURN)가 들어가면
안 됩니다. OxOa도 1문자로 카운트되기 때문입니다.
======================================
NUMBER 와 DATE TYPE 데이타 LOAD
======================================
---------
NUMBER 와 DATE TYPE 데이타 LOAD 예제이다.
Explanation
-----------
1. DATA FILE<ul2.dat>
2. CONTROL FILE<ul2.ctl>
INFILE 'ul2.dat'
INTO TABLE EMP
APPEND
( EMPNO POSITION(01:04) INTEGER EXTERNAL,
SAL POSITION(06:10) DECIMAL EXTERNAL ":SAL/100",
HIREDATE POSITION(12:17) DATE "YYMMDD",
DEPTNO POSITION(19:20) INTEGER EXTERNAL)
3. LOADER 실행
4. TABLE 구조
------------------------------- -------- ----
EMPNO NOT NULL NUMBER(4)
SAL NUMBER(7,2)
HIREDATE DATE
DEPTNO NUMBER(2)
5. 실행결과
---------- ---------- ------------------ ----------
7566 312.37 10-OCT-96 20
====================================================================================
ORACLE 8i SQL*LOADER DATAFILE의 특정 FIELD DATA를 SKIP하고 LOADING하는 방법
====================================================================================
아래의 예제와 같이 가변 길이의 filed들이 ',', '|' 와 같은 구분자로
구분이 되고 있는 경우 oracle 8i부터 제공되는 'FILLER'라고 하는 필드
구분자를 사용하여 상태인식자로 표시하여 insert시 skip할 수 있다.
TABLE : skiptab
===========================
col1 varchar2(20)
col2 varchar2(20)
col3 varchar2(20)
LOAD DATA
INFILE skip.dat
INTO TABLE skiptab
FIELDS TERMINATED BY ","
(col1 char,
col2 filler char,
col3 char)
SMITH, DALLAS, RESEARCH
ALLEN, CHICAGO, SALES
WARD, CHICAGO, SALES
$sqlldr scott/tiger control=skip.ctl
-------------------------------------------------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
또다른 예 readme.ctl
SQL*LOADER를 이용하여 TEXT FILE을 INSERT하는 방법
======================================================
여기서는 README.DAT 화일을 DATABASE의 README 테이블에 넣는 경우를 예로 들기로
한다.
NAME NULL TYPE
----------------------
NAME VARCHAR2(10)
TEXT LONG
LOAD DATA
INFILE readme.dat "fix 65535"
APPEND
CONCATENATE 10
PRESERVE blanks
INTO TABLE readme
(name position(1:10) char,
text char(65535))
원래의 README.DAT 앞에 10자리로 NAME 컬럼 내용을 추가하였음
단, 최대로 입력 가능한 TEXT FILE의 크기는 200KB 이다.
SQL*LOADER에서 SEQUENCE 함수와 DECODE 함수 사용하는 방법
================================================================
PURPOSE
---------
사용에 대해 알아보고자 한다.
-----------
table의 data에 unique한 값을 넣기 위해 sequence을 만들어 사용한다. 이것은
블루틴 #10863 을 참고하면 알수 있다. 이때 loader에서 database의 sequence기능이
아닌 sequence() 함수를 사용하는 것을 테스트해 보고자 한다.
또한 decode 함수를 사용하는 내용을 여기서 다루고자 한다.
인 경우는 적용되지 않음을 주의하자.
함수를 사용할때 참조하고자 하는 컬럼앞에 콜론(:)을 붙이면 된다.
단 무시되거나 잘못되어 들어가지 않은 레코드에 대해서는 증가을 하지 않기
때문에 주의하여야 한다.
SEQUENCE(n,increment) - 지정한 n 값부터 시작하여 increment 값만큼 증가한다.
시작하여 increment 값만큼 증가한다.
값만큼 증가한다.
Examples
----------
(field1 number, field2 number, field3 varchar2(10));
load data
infile 'data1.dat'
into table t1
fields terminated by "," optionally enclosed by '"'
(field1 SEQUENCE(MAX,1),
field2,
field3 )
3456, "CDF"
---------- ---------- --------------------
1 1234 ABC
2 3456 CDF
(test1 varchar2(10), test2 varchar2(10));
infile 'data2.dat'
into table testldr
fields terminated by ',' optionally enclosed by '"'
(test1,
test2 "decode(:test1, 'hello', 'goodbye', :test1)")
hello,""
goodbye,""
hey,""
hello,""
-------------------- --------------------
hello goodbye
goodbye goodbye
hey hey
hello goodbye
SQL*LOADER에서 | (PIPE LINE)을 RECORD SEPARATOR로 사용하기
==================================================================
-------
-----------
newline 등)였다. 이전에는 VAR 또는 FIX 등의 적당한 file을 다루기 위한 옵션을
주어야 하기 때문에 복잡한 감이 있었고 flexible하지 못했다.
되었다. newline 또는 carriage return 문자를 포함하는 data 또는 special 문자를
포함하는 data를 load하고자 할때 record terminator를 hexadecimal로 지정하여 활용할 수 있다.
--------
record separator를 사용하기 위해서 SQL*Loader의 control file에 'infile'절에 적당한 값을
아래의 예는 '|' (pipe line)을 사용하기 위해서
"str X'7c0a'"을 'infile'절에 지정하였다.
infile 'test.dat' "str X'7c0a'"
into table test
fields terminated by ',' optionally enclosed by '"'
(col1, col2)
--datafile: test.dat
this is the second|
2,this is the first line of the second record
this is the second|
SQL> desc test
Name Null? Type
----------------------------------------- -------- -----------
COL1 VARCHAR2(4)
COL2 VARCHAR2(100)
$ sqlldr scott/tiger control=test.ctl log=test.log
제대로 들어간 것을 볼 수 있다.
----
COL2
--------------------------------------------------------------------------------
1
this is the first line of the first record
this is the second
this is the first line of the second record
this is the second
특별한 값(SEQUENCE, 십진수, USERNAME)을 갖는 COLUMN을 LOADER로 넣기
===========================================================================
PURPOSE
---------
특별한 값(SEQUENCE, 십진수, USERNAME)을 갖는 COLUMN을 SQL*LOADER를
이용하여 INSERT하는 방법에 대해 알아보도록 한다.
Explanation
-----------
마지막 거래액은 함축적 의미를 가진 숫자값이다.
아래의 예에서 실제 값은 100.73, 75.25, 820.00이다.
DEPOSIT 10012 7525
WITHDRAWAL 10015 82000
(tx_type CHAR(15),
acct NUMBER,
amt NUMBER);
LOAD DATA
INFILE 'month.dat'
INTO TABLE register
(tx_type POSITION(1:10),
acct POSITION(13:17),
amt POSITION(20:24) ":amt/100")
VALUES (:data1, :data2, ":amt/100");
그러나 이 방법은 direct path 방식에서는 사용할 수 없다.
2. database sequence를 load하기
CREATE TABLE load_db_seq_positional
(seq_number NUMBER,
data1 NUMBER,
data2 CHAR(15);
CREATE SEQUENCE db_seq
START WITH 1
INCREMENT BY ;
- control file
LOAD DATA
INFILE *
INTO TABLE load_db_seq_positional
(seq_number "db_seq.nextval",
data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
select * from load_db_seq_positional;
---------- ------- -----------
1 11111 AAAAAAAAAA
2 22222 BBBBBBBBBB
[예제2]
있다는 것이다.
경우 마지막 field인 seq_number값을 넣어 주어야 하는데 그 값이 없을 경우
TRAILING NULLCOLS를 이용하여 loader에게 지정해 주어야 한다.
trailing columns는 null or non_existent이다.
사용하여 다음을 실행한다.
- control file
LOAD DATA
INFILE *
INTO TABLE load_db_seq_delimited
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(data1,
data2,
seq_number "db_seq.nextval"
)
BEGINDATA
11111,AAAAAAAAAA
22222,BBBBBBBBBB
- 결과
select * from load_db_seq_delimited;
---------- ------- ------
3 11111 AAAAAAAAAA
4 22222 BBBBBBBBBB
VALUES (:data1,data2,"db_seq.nextval");
다음 방법은 delimiter로 구분된 것으로 좀 더 신경을 써야 한다.
두 경우 모두 "USER" pseudo-variable을 사용하는데 만약 user ID를
사용하려면 "UID"를 대신 사용한다.
CREATE TABLE load_user_positional
(username CHAR(30),
data1 NUMBER,
data2 CHAR(15);
- control file
LOAD DATA
INFILE *
INTO TABLE load_user_positional
(username "USER",
data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
- 실행
sqlldr scott/tiger load_user_d.ctl
- 결과
select * from load_user_positional;
---------- ------- ----------
SCOTT 11111 AAAAAAAAAA
SCOTT 22222 BBBBBBBBBB
이 경우는 comma delimiter로 구분된 경우이다. 이경우 sql*loader는
USERNAME field를 찾을 것이다.
그러나 그 값이 없으므로 USERNAME FIELD는 CONTROL FILE 제일 뒤에 둔다.
그리고 "TRAILING NULLCOLS"절로 뒤의 null또는 존재하지 않는 값을
trailing columns라는 것을 나타내 준다.
CREATE TABLE load_user_delimited
(username CHAR(30),
data1 NUMBER,
data2 CHAR(15);
- control file
LOAD DATA
INFILE *
INTO TABLE load_user_delimited
FIELD TERMINATED BY ","
TRAILING NULLCOLS
(data1,
data2,
username "USER"
)
BEGINDATA
11111,AAAAAAAAAA
22222,BBBBBBBBBB
- 실행
sqlldr jack/jack load_user_d.ctl
select * from load_user_delimited;
---------- ------- ----------
JACK 11111 AAAAAAAAAA
JACK 22222 BBBBBBBBBB
VALUES(:data1,:data2,USER);
SQL*LOADER 성능 향상 기법
============================
---------
이 문서는 SQL*LOADER 사용시 성능 향상 기법에 대해서 설명한다.
Explanation
-----------
데이터를 올린 후 index를 생성한다.
2) B*Tree가 잘 balance 된다.
못하는 경우도 있다.
옵션 대신 TRUNCATE 옵션을 사용한다.
REPLACE 옵션을 사용할 경우 'DELETE FROM' 이라는 SQL 문장이 실행되게
되는데, 이 경우 많은 양의 redo log 및 rollback 데이터가 발생하게 된다.
반면 TRUNCATE 옵션을 사용할 경우 단순히 테이블을 truncate시키게 되므로
redo log 및 rollback 정보가 발생하지 않는다.
line 상에서 commit을 할 row 수의 최적화된 값을 지정할 수 있다. 이것은
시스템의 메모리에 따라 달리 지정하게 되는데 ROWS = n (n은 사용자가 지정하는
값)으로 지정하면 된다. 기본 값은 64이다. array 처리를 하여 속도가 개선되는
것처럼 많은 양의 row를 처리한 후 commit을 하면 그 만큼 데이터베이스에 대한
request가 줄어들게 된다.
파일의 크기가 작을 경우 redo log를 switch할 때 마다 LGWR에서 timeout이
발생하여 SQL*Loader에서 hang이 발생할 수 있다. 이와 같은 상황이 발생할 경우
LGWR trace 파일이나 alert! log에 "file busy" 또는
"can not advance to log sequence"라는 메시지가 남는다.
속도 개선이 가능하다. bind array 크기는 (#rows)*(maximum row size in bytes)
값과 같다.
이것은 파라미터 BINDSIZE 값을 지정하여 설정할 수 있다. ROWS 값도 적절하게
큰 값을 사용하여야 한다. 그렇지 않을 경우 SQL*Loader에서는 기본값을 사용하게
된다.
two-task에서 발생하는 overhead를 줄일 수 있다. 보통 benchmark test등에
적절하다.
크기가 충분히 큰지 여부를 확인하여야 한다. 만약 tablespace에 freespace가
없거나 적은 갯수의 rollback segment밖에 없을 경우에는 SQL*Loader가 hang이
걸리거나 에러가 발생하게 된다.
않고 데이터파일에 바로 write된다. SQL engine을 거치지 않으므로 rollback
segment도 사용되지 않는다.
만약 DIRECT PATCH 옵션을 사용중이라면 다음과 같은 옵션을 사용하여 추가적인
속도 개선을 얻을 수 있다.
않는다. 테이블에 index가 걸려 있을 경우에는 index에 대한 데이터는 redo log에
기록된다.
있다면 SORTED INDEX절을 사용할 수 있다.
2) SORTED INDEX 절을 사용하는데 데이터가 올바로 정렬되어 있지 않다면
인덱스의 status가 DIRECT LOAD 상태로 남게 된다.
도 parallel하게 loading 될 수 있다.
SQL*LOADER 실행 시 발생하는 ORA-1653
=======================================
---------
다음은 SQL*LOADER 실행시 ORA-1653 ERROR가 발생시에 조치하는
방법을 설명한다.
Explanation
-------------
extent가 일어나지 못해서 발생하는 error 이다 .
먼저 error message 에서 tablespace name 이 무엇인지 먼저
check 한다.
tablespace 가 잡혀있지 않기 때문이어서 근본적인 해결이 필요하다.
tablespace 를 create 후 user 에게 할당해주도록 한다.
DEFAULT TABLESPACE tablespace_name
TEMPORARY TABLESPACE temp ;
tablespace 안에 create table을 다시 한 후 sql*loader 를 실행한다.
음수 부호가 뒤에 있을 경우 LOADER로 숫자 DATA를 올리는 방법
===========================================================
PURPOSE
---------
음수 부호가 뒤에 있을 경우 LOADER로 숫자 DATA를 올리는 방법에
대해 알아보도록 한다.
Explanation
-----------
간혹 SQL*LOADER를 사용하는 사람들 중에 data file 내의
숫자 field의 부호가 뒤에 붙어 있거나 특정한 값을 읽어
그 값이 무엇인가에 따라 숫자를 음수와 양수로 만들어야
하는 경우가 있다. 이경우에는 SQL*LOADER의 기능으로는
해결방법이 없으나 다음과 같이 SQL 함수를 사용해서 해결
이 가능하다.
create table test_table
(a number(3), b number(3));
그리고 data가 있는 test.dat는 다음과 같다고 가정한다.
100-,3
222,1
============
동작시키면 원하는 결과를 얻을수 있다.
============
load data
infile 'test.dat'
replace
into table test_table
fields terminated by ","
trailing nullcols
(a integer external "decode(substr(:a,-1),'-',rtrim(:a,'-')*-1,:a)",
b integer external)
======================
A B
------ -----
-100 3
222 1
======================================================
TAPE로 EXPORT, IMPORT!, LOADER 사용하기(PIPE 사용)
======================================================
Purpose
-------
대용량의 DATA를 BACKUP 받거나 DATA를 처리할 때에는 TAPE을 이용하는
경우가 있다. 이럴 때 EXPORT, IMPORT!, SQL*LOADER에서 TAPE 를 이용하는
방법을 종류별로 정리하였다.
Explanation
-----------
SIZE이다. 만약 첫번 째 TAPE이 245M에 이르게 되면 다음 TAPE를 넣으라는
메시지가 나온다.
2. Tape device에서 import!받기
3. PIPE와 DD를 이용한 tape의 export
% dd if=/tmp/exp_pipe of=<tape device> & # Write from pipe to tape
% exp file=/tmp/exp_pipe <other options> # Export to the pipe
4. PIPE와 DD를 이용한 tape의 import!
% dd if=<tape device> of=/tmp/imp_pipe & # Write from tape to pipe
% imp file=/tmp/imp_pipe <other options> # Import! from the pipe
5. PIPE 와 DD를 이용한 remote server의 tape device에 export하기
% dd if=/tmp/exp_pipe | rsh <hostname> dd of=<file or device> &
% exp file=/tmp/exp_pipe <other options>
6. PIPE 와 DD 를 이용한 remote server의 tape device에서 import!하기
% rsh <hostname> dd if=<file or device | dd of=/tmp/imp_pipe &
% imp file=/tmp/imp_pipe <other options>
7. TAPE에 있는 DATA FILE을 SQL*LOADER로 받기.
% dd if=<tape_device> of=/tmp/load_pipe &
% sqlldr userid=user/pass control=contol.ctl log=loader.log
infile='/tmp/load_pipe'
화일이다. PIPE buffer는 Sun Solaris에서는 5K, HP에서는 8K, SGI에서는
10K이다. 이것은 FIFO를 따르며 command는 다음과 같다.
LOADER에서 MAXIMUM SPECIFIED LENGTH
===========================================
PURPOSE
---------
sql*loader로 작업시 "Field in data file exceeded maximum specified
length" 메세지가 발생하는 원인과 해결 방안에 대해 알아보도록 한다.
Explanation
-----------
sql*loader로 작업을 하다보면 varchar2 column이 255 character가 넘는
경우에 "Field in data file exceeded maximum specified length"라는
message를 .log에 남기면서 해당 field가 reject되는 경우가 있다.
이경우의 해결 방법은 control file의 column의 길이를 "col1 char(300)"
처럼 기술해 주면 된다.
loader는 이 buffer에 얼마나 많은 row를 넣을 것인지를 결정하기 위하여
control file 내의 column의 최대길이에 대한 계산을 하게 된다. 이때 그
값이 data type에 의하거나 명시적으로 기술되어 있지 않으면 loader는
나름대로의 길이를 가정하게 되는데 이때 char값은 255로 가정되게 된다.
그러므로 255가 넘는 값은 위에서와 같은 error를 만들게 된다.
loader의 효율을 높일수 있다는 것이다. 명시된 값은 그 길이만큼 buffer에
넣을때 적용이 되므로 255보다 적은 값이 결과적으로 buffer에 들어갈 filed의
길이를 적게 만들어서 한번에 더 많은 row를 load할 수 있게 한다.
CONVENTIONAL PATH LOAD & DIRECT PATH LOAD
===================================================
사용할 수 있다. 여기에서 이러한 direct path load의 자세한 개념 및 사용방법,
사용 시 고려해야 할 점 등을 설명한다.
1. conventional path load
SQL의 INSERT command를 이용하여 insert시킨다. 이렇게 SQL command를
이용하기 때문에 각각의 데이타를 위한 insert command가 생성되어 parsing되는
과정이 필요하며, 먼저 bind array buffer (data block buffer) 내에 insert되는
데이타를 입력시킨 후 이것을 disk에 write하게 된다.
--- load 중에 table을 index를 이용하여 access하여야 하는 경우
direct load중에는 index가 'direct load state'가 되어 사용이 불가능하다.
--- load 중에 index를 사용하지 않고 table을 update나 insert등을 수행해야 하는 경우
direct load 중에는 table에 exclusive write(X) lock을 건다.
--- SQL*NET을 통해 load를 수행해야 하는 경우
--- clustered table에 load하여야 하는 경우
--- index가 걸려 있는 큰 table에 적은 수의 데이타를 load하고자 할 때
--- referential이나 check integrity가 정의되어 있는 큰 table에
load하고자 할 때
--- data field에 SQL function을 사용하여 load하고자 할 때
2. direct path load의 수행 원리
빠른 시간에 load하고자 할 때 이용하는 것이 바람직하다.
(2) memory 내의 bind array buffer를 이용하지 않고 database block의
format과 같은 data
block을 memory에 만들어 데이타를 넣은 후 그대로 disk에 write한다.
memory 내의 block buffer와 disk의 block은 그 format이 다르다.
(3) load 시작 시에 table에 lock을 걸고 load가 끝나면 release시킨다.
(4) table의 HWM (High Water Mark) 윗 부분의 block에 data를 load한다.
HWM는 table에 data가 insert됨에 따라 계속 늘어나고 truncate 외에는
줄어들게 하지 못한다.
그러므로, 항상 완전히 빈 새로운 block을 할당받아 data를 입력시키게 된다.
(5) instance failure가 발생하여도 redo log file을 필요로 하지 않는다.
(6) UNDO information을 발생시키지 않는다.
즉 rollback segment를 사용하지 않는다.
(7) OS에서 asynchronous I/O가 가능하다면, 복수개의 buffer에 의해서 동시에
data를 읽어서 buffer에 write하면서 buffer에서 disk로 write할 수 있다.
(8) parallel option을 이용하면 더욱 성능을 향상시킬 수 있다.
3. direct path load의 사용방법 및 options
미리 sys user로 수행되어야 한다. 단 이 script는 catalog.sql에 포함되어 있어,
db 구성 시에 이미 수행되어진다.
포함시키기만 하면 된다. 다음과 같이 기술하면 된다.
내에 기술 가능한 clause들을 살펴본다.
conventional path load에서 rows는 default가 64이며, rows에 지정된 갯수
만큼의 row가 load되면 commit이 발생한다. 이와 비슷하게 direct load
path에서는 rows option을 이용하여 data save를 이루며, data save가 발생하면
data는 기존 table에 포함되어 입력된 data를 잃지 않게 된다.
단 이 때 direct path load는 모든 data가 load된 다음에야 index가
구성되므로 data save가 발생하여도 index는 여전히 direct load state로
사용하지 못하게 된다.
direct path load에서 이 rows의 default값은 unlimited이며, 지정된 값이
database block을 채우지 못하면 block을 완전히 채우는 값으로 올림하여,
partial block이 생성되지 않도록 한다.
control file내에 column_spec datatype_spec PIECED 순으로 기술하는
것으로서 direct path load에만 유효하다. LONG type과 같이 하나의 data가
maximum buffer size보다 큰 경우 하나의 data를 여러번에 나누어 load하는
것이다. 이 option은 table의 맨 마지막 field
하나에만 적용가능하며, index column인 경우에는 사용할 수 없다.
그리고 load도중 data에 문제가 있는 경우 현재 load되는 data의 잘린 부분만
bad file에 기록되게 된다. 왜냐하면 이전 조각은 이미 datafile에 기록되어
buffer에는 남아있지 않기 때문이다.
만약 매우 큰 data가 마지막 field가 아니거나 index의 한 부분인 경우
PIECED option을 사용할 수 없다. 이러한 경우 buffer size를 증가시켜야
하는데 이것은 readbuffers option을 이용하면 된다. default buffer갯수는
4개이며, 만약 data load중 ORA-2374(No more slots for read buffer
queue) message가 나타나면, buffer갯수가 부족한 것이므로 늘려주도록 한다.
단 일반적으로는 이 option을 이용하여 값을 늘린다하더라도 system
overhead만 증가하고 performance의 향상은 기대하기 어렵다.
4. direct path load에서의 index 처리
(2) load된 data의 key 부분이 temporary segment에 copy되어 sort된다.
(3) 기존에 존재하던 index와 (2)에 의해서 정렬된 key가 merge된다.
(4) (3)에 의해서 새로운 index가 만들어진다.
기존에 존재하던 index와 temporary segment, 그리고 새로 만들어지는 index가
merge가 완전히 끝날 때까지 모두 존재한다.
(5) old index와 temporary segment는 지워진다.
row 씩 index에 첨가된다. 그러므로 temporary storage space는 필요하지 않지만
direct path load에 비해 index 생성 시간도 느리고, index tree의 balancing도
떨어지게 된다.
있다.
key_storage = (number_of_rows) * (10 + sum_of_column_sizes +
number_of_columns)
전체 data가 완전히 순서가 거꾸로 된 경우에는 2, 전체가 미리 정렬된 경우라면
1을 적용하면 된다.
이와 같이 direct path load에서 index 생성 시 space를 많이 차지하는 문제점
때문에 resource가 부족한 경우에는 SINGLEROW option을 사용할 수 있다.
이 option은 controlfile 내에 다음과 같은 형태로 기술하며, direct path
load에만 사용 가능하다.
data가 load됨에 따라 data 각각이 바로 index에 추가된다.
이 option은 기존에 미리 index가 존재하는 경우 index를 생성하는 동안
merge를 위해 space를 추가적으로 필요로 하는 것을 막고자 하는 것이므로
INSERT 시에는 사용하지 않고, APPEND시에만 사용하도록 하고 있다.
실제 새로 load할 data 보다 기존 table이 20배 이상 클 때 사용하도록 권하고
있다.
option을 사용하면 insert되는 index에 대해 undo 정보를 rollback segment에
기록하게 된다.
그러나, 중간에 instance failure가 발생하면 data는 data save까지는 보존
되지만 index는 여전히 direct load state로 사용할 수 없게 된다.
--- Direct Load State
만약 direct path load가 성공적으로 끝나지 않으면 index는 direct load
state로 된다.
이 index를 통해 조회하고자 하면 다음과 같은 오류가 발생한다.
ORA-01502 : index 'SCOTT.DEPT_PK' is in direct load state.
(1) index가 생성되는 과정에서 space가 부족한 경우
(2) SORTED INDEXES clause가 사용되었으나, 실제 data는 정렬되어 있지 않은
경우
이러한 경우 data는 모두 load가 되고, index만이 direct load state로 된다.
(3) index 생성 도중 instance failure가 발생한 경우
(4) unique index가 지정되어 있는 컬럼에 중복된 data가 load되는 경우
from user_indexes
where table_name = TABLE_NAME';
하여야만 사용할 수 있다. 단, direct load 중에는 모든 index가 direct
load state로 되었다가 load가 성공적으로 끝나면 자동으로 valid로 변경된다.
direct load 시 index구성을 위해서 정렬하는 시간을 줄이기 위해 미리 index
column에 대해서 data를 정렬하여 load시킬 수 있다. 이 때 control file 내에
SORTED INDEXES option을 다음과 같이 정의한다.
이 option은 direct path load 시에만 유효하며, 복수 개의 index에 대해서
지정가능하다.
의 temporary storage가 필요하며, 기존 index가 없는 경우였다면, 이러한
temporary space도 필요하지 않다.
load하는 경우 space도 추가적으로 들고, load가 완전히 성공적으로 끝나지 않으면
index를 재생성하여야 하므로, 일반적으로 direct path load 전에 미리 table의
index를 제거한 후 load가 모두 끝난 후 재생성하도록 한다.
5. Recovery
새로운 block을 할당받아 정확히 write가 끝난 다음 해당 segment에 포함되기
때문에 instance failure시에는 redo log정보를 필요로 하지 않는다. 그러나
default로 direct load는 redo log에 입력되는 data를 기록하는데 이것은 media
recovery를 위한 것이다. 그러므로 archive log mode가 아니면 direct load에
생성된 redo log 정보는 불필요하게 되므로 NOARCHIVELOG mode시에는 항상
control file내에 UNRECOVERABLE이라는 option을 사용하여 redo log에 redo entry를 기록하지
data가 redo log 정보 없이 instance failure시에 data save까지는 보호되는데
반해 index는 무조건 direct load state가 되어 재생성하여야 한다. 그리고 data save이후의 load
6. Integrity Constraints & Triggers
상태로 존재한다. not null은 insert시에 check되고 unique는 load후 index를
구성하는 시점에 check된다.
그러나 check constraint와 referential constraint는 load가 시작되면서
disable상태로 된다. 전체 데이타가 load되고 난 후 이렇게 disable된
constraints를 enable시키려면 control file내에 REENABLE이라는 option을
지정하여야 한다. 이 reenable option은 각 constraint마다 지정할 수는 없으며
control file에 한번 지정하면 전체 integrity/check constraint에 영향을
미치게 된다. 만약 reenable되는 과정에서 constraint를 위배하는 data가
발견되면 해당 constraint는 enable되지 못하고 disabled status로 남게 되며,
이렇게 위배된 data를 확인하기 위해서는 reenable clause에 exceptions option을 다음과 같이
directory로copy하여 table이름을 exceptions가 아닌 다른 이름으로 만들어 수행시키면 된다.
insert trigger도 integrity/check constraint와 같이 direct load가 시작하는
시점에 disable되며, load가 끝나면 자동으로 enable된다. 단 enable되고 나서도
load에 의해 입력된 data에 대해 trigger가 fire되지는 않는다.

