오라클 데이터베이스를 export/import 하기 위해서 Original Utility인 exp/imp 또는 10g 이후부터 새로 나온 Oracle Data Pump (expdp/impdp)를 주로 사용합니다. 하지만 스키마 만을 옮기거나 데이터가 작을 경우 sqldeveloper를 사용해서 간단하게 export/import 할 수 있습니다.
sqldeveloper를 사용하여 export 하는 방법을 알아 보겠습니다. 이 글의 테스트는 Oracle 11g express editon과 SQL Developer 17.2를 사용하였고 샘플 데이터베이스는 전자정부 표준프레임워크 Portal Site 템플릿의 테이블을 사용하였습니다.
1. SQL Developer 메뉴에서 도구(T) -> 데이터베이스 익스포트(X)... 를 선택하여 익스포트 마법사를 실행합니다.
2. 접속 셀렉트 박스를 열어서 sqldeveloper에 등록해둔 계정(schema)중에서 작업할 계정을 선택합니다.
3. DDL 익스포트 부분을 체크하면 데이터베이스 객체(테이블, 뷰, 제약사항 등)를 생성하는 스크립트가 만들어 집니다. 데이터베이스를 옮길때 고려해야 할 사항이 몇가지 있습니다.
- 스키마 표시(S) : 스크립트에 현재 스키마가 포함되어 생성됩니다. 옮기려고 하는 데이터베이스에 스키마(아이디) 가 다르다면 체크를 해제 하면 되겠습니다.
- 저장 영역(G) : 스크립트에 테이블스페이스와 storage 관련 구문들이 포함되어 집니다. 옮기려는 데이터베이스의 테이블스페이스가 다르다면 생성 후 수정을 하거나, 체크를 해제하고 생성해서 테이블스페이스 부분은 수동으로 추가해서 사용해야 겠습니다. 만약 옮기려는 데이터베이스 버전이 현재 데이터베이스와 다르다면(11g 에서 10g 로 옮기는 등) storage 지정 구문이 달라서 import 시 문법 오류가 발생할 수 있습니다. 이런 상황이 발생한다면 스크립트 생성후에 직접 수정하여 사용하여야 겠습니다.
아래 코드는 두가지 모두 체크했을때와 하지 않았을때 생성되는 테이블 생성 스크립트의 예 입니다.
-- 체크해제시 생성 스크립트
CREATE TABLE "COMTCCMMNCLCODE"
( "CL_CODE" CHAR(3 BYTE),
...
"LAST_UPDUSR_ID" VARCHAR2(20 BYTE)
) ;
-- 체크시 생성 스크립트
CREATE TABLE "EGOVPORTALUSER"."COMTCCMMNCLCODE"
( "CL_CODE" CHAR(3 BYTE),
...
"LAST_UPDUSR_ID" VARCHAR2(20 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
4. 데이터 익스포트(O) 부분을 체크하고, 설정합니다.
- 형식 : 데이터 익스포트 형식을 선택합니다. 여러종류가 있지만 자주 사용될만한 몇가지만 알아보겠습니다.
* insert : 데이터를 insert sql 문으로 만들어 줍니다. 주의할 사항은 테이블에 CLOB 타입의 필드는 제외 됩니다.
* csv : 데이터가 콤마로 분리된 텍스트파일로 만들어 줍니다.
* excel 2003+(xlsx) : 엑셀 .xlsx 파일로 만들어 줍니다.
* excel 95-2003(xls) : 엑셀 .xls 파일로 만들어 줍니다.
* loader : SQL Loader 용 컨트롤 파일과 데이터 파일로 만들어 줍니다.
insert 스크립트로 만드는 것이 나중에 import 할 때 간편하기는 한데, CLOB 데이터가 포함되어 있다면 다른 방법을 선택해야 겠습니다. insert 스크립트로 옮긴 후, CLOB 필드를 가지는 테이블 데이터만 따로 다른 형식으로 옮겨도 되겠습니다. 엑셀 형식으로 CLOB 데이터를 옮길 경우 주의할 점은 엑셀의 한 셀(cell)에 포함될 수 있는 최대 글자수가 32,767자 이므로 이것을 넘으면 어떨지 주의해야 겠습니다. (이 부분은 테스트해보지 못했습니다.)
- 다음마다 커밋 포함 : 이부분은 데이터가 많을 때 사용하면 됩니다. 데이터가 얼마되지 않으면 모두 입력후에 커밋해도 되지만 데이터가 많다면 입력하는 중간 중간 커밋을 하는것이 속도면에서 유리합니다.
5. 다른 이름으로 저장(V) 을 선택합니다. 여러 항목이 있지만, 세가지만 알아보겠습니다.
- 단일 파일 : 익스포트 데이터를 하나의 파일로 만들어 줍니다. 형식이 insert 일때만 사용할 수 있습니다.
- 별도의 파일 : 테이블, 제약사항 등 모두 데이터베이스 객체를 별도의 파일로 만들어 줍니다. 데이터는 테이블 별로 별도의 파일에 생성됩니다.
- 유형 파일 : 테이블, 인덱스, 제약사항 등 유형별 파일로 생성스크립트를 만들어줍니다. 데이터는 테이블 별로 별도의 파일에 생성됩니다.
7. 인코딩 을 선택합니다. 생성될 파일의 인코딩입니다. 백업일 경우는 현재 데이터베이스의 캐릭터셋에 맞추고 옮기는 경우는 대상 데이터베이스의 캐릭터셋에 맞추면 되겠습니다.
8. 파일(F) 항목에서 생성될 파일 이름을 지정합니다. insert 형식에서 단일 파일로 저장할 경우 파일명을 지정하고, 별도의 파일이나 유형 파일을 사용할 경우에는 폴더를 지정합니다.
지정을 모두 하였으면 다음을 눌러 두번째 단계로 진행합니다.
9. 마법사 두 번째 단계인 익스포트할 유형을 선택하는 단계입니다. 표준객체 유형에서 익스포트할 항목만 체크하면 됩니다.
10. 다음은 세 번째 단계인 익스포트할 객체를 지정하는 화면 입니다. 상단의 조회(K) 를 눌러서 보여지는 리스트중에서 선택하고, 중간의 오른쪽/왼쪽 화살표를 눌러 우측으로 옮기면 됩니다.
11. 네 번째 단계는 테이블 데이터중에 특정 열을 선택하면 익스포트 할 수 있습니다. 테이블의 열 항목을 클릭하면 연필 모양의 아이콘이 생기는데 이것을 클릭하면 열을 선택할 수 있는 창이 뜹니다. 거기에서 익스포트할 열을 선택합니다. 기본은 전체 익스포트 입니다.
12. 다음 단계는 앞에서 설정한 내용의 요약정보를 보는 창입니다.
13. 완료를 눌러 익스포트 합니다.
익스포트가 완료되면 sqldeveloper 창의 워크시트에 결과물이 열립니다. insert 형식으로 단일파일로 했다면 스크립트 파일이 열리고, 별도의 파일이나 유형파일로 익스포트 했다면 유형 파일이 열립니다. 다음은 xlsx 형식과 유형 파일로 익스포트 할때 생성된 import 스크립트 예 입니다. sqldeveloper 워크시트에서 @하고 스크립트 파일을 지정하여 그 파일을 실행할 수 있습니다.
insert 문으로 데이터를 익스포트한 경우가 아니라면, 워크시트에서는 DDL 문만을 실행하여 테이블을 생성하고 데이터 import는 별도의 작업을 거쳐야 합니다.
--------------------------------------------------------
-- 파일이 생성됨
--------------------------------------------------------
@E:\export\VIEWS.sql
@E:\export\TABLES.sql
--@E:\export\LETTNBBS_DATA_TABLE.xlsx
--@E:\export\COMVNUSERMASTER_DATA_VIEW.xlsx
@E:\export\INDEXES.sql
@E:\export\CONSTRAINTS.sql
@E:\export\REF_CONSTRAINTS.sql
이제 데이터를 import 하는 방법을 보겠습니다.
1. insert 문으로 익스포트한 경우
이 경우는 별다른 설명이 필요 없이 간단합니다. 워크시트에서 "@스크립트 파일명" 을 실행해서 할 수도 있고, 익스포트 파일내용을 워크시트로 복사 붙여넣어서 실행을 해도 됩니다.
2. 엑셀 형식 또는 csv 형식으로 익스포트한 경우
이때도 DDL 문은 sql 스크립트로 되어 있으므로 워크시트에서 실행해서 필요한 객체를 생성하면 됩니다. 데이터는 테이블 별로 import 하여야 합니다. sqldeveloper 좌측의 테이블 트리를 열고 마우스 오른쪽 키를 눌러 "데이터 임포트(A)..." 를 선택해서 데이터임포트 마법사를 시작합니다.
- 데이터 파일 임포트 항목에 임포트할 엑셀 파일을 선택합니다.
- 두 번째 단계에서 임포트 방식 지정과 데이터를 확인할 수 있습니다. 엑셀 데이터의 입력은 insert 스크립트가 만들어지고 그것이 실행되어 입력되는 방식입니다.
- 다음 단계에서 입력할 열을 선택할 수 있습니다. 기본으로 전체 선택이 되어 있습니다.
- 다음 단계에서 열 정의를 하게 됩니다. 소스의 열과 테이블의 열을 맞출 수 있습니다.
- 다음 단계는 지금까지 설정의 요약정보를 보여줍니다. 필요하면 뒤로 돌아가서 다시 설정할 수 있습니다.
- 완료를 눌러 임포트를 수행합니다.
3. SQL Loader 파일로 export 한 경우
이경우도 DDL이 sql 스크립트로 익스포트 되므로 테이블 등 데이터베이스 객체는 DDL 스크립트를 실행하여 생성하고, 데이터만 SQL Loader 를 사용하여 임포트 합니다.
SQL Loader는 데이터베이스가 설치되거나 오라클 클라이언트가 전체 설치되면 같이 설치 됩니다. instant client 에는 포함되어 있지 않습니다.
다음 명령으로 실행하면 됩니다.
E:\export>sqlldr 아이디/비밀번호@SID controls=LETTNBBS_DATA_TABLE.ctl
가끔 클라이언트와 서버의 엔코딩이 맞지않아서 익스포트시 지정한 인코딩대로 데이터가 들어가지 않고, 한글이 깨지는 경우가 있습니다. 이때는 ctl 파일에 캐릭터셋을 지정하여 실행하면 됩니다.
OPTIONS (ERRORS=50)
LOAD DATA
CHARACTERSET KO16KSC5601
INFILE 'E:\export\LETTNBBS_DATA_TABLE.ldr' "str '{EOL}'"
APPEND
...
이것으로 sqldeveloper 를 사용해서 데이터베이스를 export/import 하는 방법에 대해서 알아보았습니다.
'프로그래밍 > 데이터베이스' 카테고리의 다른 글
| Oracle에서 VARCHAR2 컬럼을 CLOB으로 변경하기 (0) | 2018.04.20 |
|---|---|
| Windows 10에 MariaDB 설치하기 (14) | 2018.04.19 |
| 새로운 Oracle export/import 유틸리티 - Oracle Data Pump(expdp/impdp) (3) | 2018.04.17 |
| Oracle XE 컴퓨터 이름(Hostname) 변경후 접속 안될 때 (2) | 2018.04.12 |
| 오라클 테이블 스페이스 생성하기 및 다른 사용자 권한 부여 (0) | 2018.04.12 |