Sqlplus spool 명령어, set 설정으로 csv 데이터 추출 방법

같은 종류의 database 이관을 할 때는 방법이 많습니다. 예를 들어, 오라클에서 오라클로 데이터 이관을 할 때를 생각해 보죠. 데이터파일을 옮겨도 되고, dblink를 써도 되고, Export, Import 툴을 사용할 수도 있어 여러 옵션이 있습니다.

하지만 이기종 간에 이행은 고려할 사항이 좀 있는데요. 요즘 AWS 클라우드에서는 DNS라는 솔루션으로 손쉽게 이관한다고 하지만, 기본적으로  db to db로 바로 연결할 수 없기 때문에 대부분의 마이그레이션 솔루션들이 파일로 떨어뜨린 다음에 Load 하는 방식을 사용합니다. 
 

SQL*Plus SPOOL 데이터 추출 가이드

 
이번에는 오라클에 SPOOL이라는 기능으로 구분자로 데이터를 추출하는 방법을 알아보도록 하겠습니다. 우리가 잘 알고 있는 csv파일로 떨어뜨린다고 이해하시면 됩니다. 
 

SPOOL이란?

SPOOL은 오라클 화면을 갈무리하여 파일로 저장하는 명령어입니다. 그래서 꼭 데이터를 백업 받기 위한 용도가 아니긴 합니다. 단지 내가 실행한 SQL문과 그 결과를 파일에 기록해 놓고 싶을 때 사용하는 목적이라고 할 수 있죠. 

그런데 SPOOL을 이용하면 오라클의 EXPORT 툴을 사용하지 않더라도 구분자로 데이터를 추출할 수 있습니다. 그리고 해당 파일을 이용해 이기종 데이터베이스에 데이터 이관을 할 수도 있습니다. 
 

SPOOL의 동작 방식

SPOOL은 SQL*Plus에서 SPOOL ‘FILE’ 명령으로 시작합니다. 내가 기록하고 싶은 SQL 실행하면 결과가 화면에 나오게 됩니다. 그리고 SPOOL OFF 명령을 내리면 그 사이에 화면에 출력된 내용이 그대로 ‘FILE’ 파일에 저장됩니다.  

• SQL실행문(STDIN) | 결과(STDIN) -> FILE 저장

아래는 SPOOL명령어를 작성한 내용입니다. 

-- spool을 위한 화면 환경 세팅 set lines 20000 pages 0 head off feed off echo off long 1000000 trimspool on term off; -- date 타입에 대한 포멧 정의 alter session set nls_date_format = 'YYYY-MM-DD-hh24:mi:ss' ; spool emp_20240718.txt   -- SQL쿼리문 SELECT * FROM EMP ;  spool off

 

SPOOL 데이터 추출 2가지 방법

데이터를 추출하는 두가지 방법이 있습니다. 첫 번째는 SET COLSEP 옵션을 사용하는 방법입니다. 아래와 같은 명령어를 이용하면 ", "로 구분하여 데이터를 추출할 수 있습니다. 

set lines 20000 pages 0 head off feed off echo off long 10000 trimspool on term off; alter session set nls_date_format = 'YYYY-MM-DD-hh24:mi:ss' ; set COLSEP , spool emp_20240718.txt   SELECT * FROM EMP ; spool off

• SET COLSEP ,
• 단점 : 쉼표(,)는 텍스트 내용 중에 자주 발생하기 때문에 다른 특수문자를 사용하는 것이 좋습니다. 또 이 방법을 사용할 경우 중간에 tab문자가 들어간다던지 하는 깔끔하게 구분되지 않기도 합니다. 

두 번째는 컬럼과 컴럼사이에 구븐자와 함께 연결(concatation) 해서 하나로 출력 방법입니다. 그리고 중간중간에 구분자를 넣어서 작업하면 하나의 컬럼으로 추출됩니다. 

set lines 20000 pages 0 head off feed off echo off long 10000 trimspool on term off; alter session set nls_date_format = 'YYYY-MM-DD-hh24:mi:ss' ; column COL_LIST format a10000 spool emp_20240718.txt   SELECT COL1||”,”||COL2 …||COL9 as COL_LIST FROM EMP ; spool off

• SELECT COL1||”,”||COL2 …||COL9 as COL_LIST FROM EMP;
• 단점 : 이 경우는 column COL_LIST format a10000 옵션을 주어야 합니다. 그래야 COL_LIST에 대해 디폴트 컬럼 크기가 잡히지 않고 전체 데이터를 추출할 수 있습니다. 만약 길이를 주지 않으면 디폴트 길이인 80 정도에서 잘려서 데이터가 내려받아집니다. 

 

SET 명령어

1. SET LINES(IZE) 20000

한 줄에 출력되는 문자 수를 제어합니다. 디폴트는 80(SQL*Plus에서는 150)입니다. 최대 32,765자까지 지정할 수 있습니다. 보통 SPOOL을 떠서 데이터 저장을 할 때는 한 row가 아래로 내려가지 않게 충분히 크게 지정하는 것이 좋습니다. 
 

2. PAGES(IZE) 0

SQL*Plus에서 출력의 한 페이지에 들어갈 줄(row)의 수를 의미합니다. 디폴트 14(SQL*Plus에서는 24)입니다. 0을 설정하면 모든 페이지 제목, 바닥글, 열 제목 및 모든 빈 줄 등이 제거됩니다. 
 

3. HEAD(DING) OFF

PRINT 시 COLUMN 제목 출력할지 여부(ON 출력, OFF 출력하지 않음) 
 

4. FEED(BACK) OFF

SELECT 레코드 수를 표시 할 지 여부를 제어합니다. 
 

5. ECHO OFF

주어진 명령어를 LOG에 표시되지 않게 하는 옵션입니다.
 

6. WRAP ON

LINESIZE 설정보다 많은 문자가 포함된 LINE을 출력할 경우 ON을 설정하면 다음 줄로 넘어갑니다. OFF를 설정하면, LINESIZE에 맞게 잘립니다. 
 

7. TERM(OUT) OFF

OFF하면 터미널 출력을 끕니다. 명령이 실행되는 동안 사용자 화면이 쿼리 출력과 피드백 메시지로 어수선해지는 것을 방지하기 위해 사용합니다. 오라클 사이트의 글을 보면 TERMOUT OFF를 하면 대화형으로 실행되는 명령의 출력에 영향을 미치지 않는다고 되어 있습니다. 

이 말을 잘못 해석해면, TERMOUT OFF와 ON의 출력 결과가 달라질 수 있을 거로 생각하는데요. 전혀 다르지 않습니다. 이 명령어는 단지 사용자 화면에 출력할지 말지에 대한 내용이며 실제로 하는 행위는 동일합니다.  
 

8. LONG 1000000

LONG열에서 표시되는 문자 수를 나타냅니다. 만약 설정하지 않으면 80자입니다. 80자 이상 되면 공백으로 나올 수 있습니다. 기본적으로 VARCHAR2 가 80이 넘는 것도 많고 특히  CLOB 타입의 경우를 위해 1000000 또는 2000000까지도 설정할 수 있습니다.
 
참고로 CLOB은 4G까지 텍스트를 저장이 가능합니다. VARCHAR2가 4000 BYTES까지만 가능하기 때문에 그 이상 데이터는 CLOB 타입을 이용하죠. 경우에 따라서는 정말 많은 문자수의 데이터도 있을 수 있다 생각하면, 누락이 없으려면 LONG을 키워주는 것을 권장합니다. 
 

9. LONGCHUNSIZE

LOGCHUNNSIZE는 조회할 때 성능 과련 설정입니다. LONG열에 대해 한번에 검색할 문자 수를 지정하는 것인데요. 디폴트가 80입니다. 만약 메모리가 허락한다면 LONG 지정한 만큼 동일하게 지정해 주면 한번에 패치해 올 수 있습니다. 
 

10. TIMESPOOL ON

각 row가 SPOOL 파일에 기록되기 전에 끝에 있는 공백을 모두 제거해 주는 옵션입니다. 예를 들어, SET LINES를 20000으로 잡았다면, TIMESPOOL ON 없으면 20000될 때까지 공백으로 채워집니다. 그리고 개행 문자가 나옵니다.
 
이렇게 되면 데이터를 Load하기 위해서는 뒤에 공백을 제거하는 명령어 또는 프로그램을 개발해야 합니다. 이 옵션을 켜면 그런 문제가 없습니다. 단, 공백을 제거하는 처리가 들어가기 때문에 SPOOL 저장 시 시간은 좀 더 걸린다고 생각하면 됩니다.