Export & Import Database in Oracle
OracleのExport/Import Utility를 이용하여 데이터를 export/import하는 방법을 소개한다.
Export Utility
Export명령의 syntax는 다음과 같다.
exp 사용자ID/암호@오라클인스턴스명 file=백업할 파일명 indexes=yes grants=yes constraints=yes
Export 단위는 사용자 혹은 테이블 단위가 되며 tables 옵션을 생략한 경우 default 로 사용자 단위 export 를 수행한다.
사용할 수 있는 옵션에는 다음과 같은 것들이 있다.
| option | 내용 | 비고 |
|---|---|---|
| -userid | EXPORT를 실행시키고 있는 username/password명. | |
| -buffer | 데이터 행들을 가져오는데 사용되는 버퍼의 크기. | |
| -file | 생성되는 EXPORT덤프 파일명. | |
| -filesize | EXPORT덤프 파일의 최대 크기 | |
| -grants | 데이터베이스 객체에 대한 권한 정보의 EXPORT여부 (Y/N 플래그) | |
| -indexes | 테이블에 대한 INDEXES의 EXPORT여부 (Y/N 플래그) | |
| -rows | 행들에 대한 EXPORT여부. (Y/N 플래그) | 만약 “no”이면 데이터는 EXPORT되지않고 테이블의 정의만 EXPORT |
| -constraints | 테이블에 대한 제약조건 정보의 EXPORT여부 (Y/N 플래그) | |
| -compress | IMPORT에 대비하여 테이블의 데이터를 한 extent로 압축 할것인가의 여부(Y/N 플래그) | |
| -full | 전체 데이터베이스를 EXPORT할것인가의 여부 (Full Level Export) (Y/N 플래그) | |
| -owner | EXPORT될 데이터베이스의 소유자명 (User Level Export)[owner=user] | |
| -tables | export될 테이블의 리스트(Table Level Export) [tables=(table1, table2, …)] | |
| -statistics | 통계정보의 생성유무(estimate/none) | warning을 무시할경우 none |
Import Utility
Import명령어의 Syntax는 다음과 같다
imp userid="오라클사용자명/암호@인스턴스명" file='덤프파일이름' fromuser=익스포트한사용자ID touser=임포트할ID
사용할 수 있는 옵션에는 다음과 같은 것들이 있다.
| option | 내용 | 비고 |
|---|---|---|
| -userid | IMPORT를 실생시키는 계정의 username/password명 | |
| -buffer | 데이터를 행들을 가져오는데 사용되는 buffer의 bytes수 | |
| -file | IMPORT될 EXPORT 덤프 파일명 | |
| -show | 파일 내용이 화면에 표시되어야 할 것인가를 나타냄(Y/N 플래그) | |
| -ignore | IMPORT중 CREATE명령을 실행할 때 만나게 되는 에러들을 무시할 것인지 결정(Y/N 플래그) | |
| -indexes | 테이블 INDEX의 IMPORT여부(Y/N 플래그) | |
| -rows | 테이블 데이터를 IMPORT할 것인가(Y/N 플래그) | 만약 “N”로 설정하면 데이터베이스 객체들에 대한 DDL만이 실행 |
| -full | FULL엑스포트 덤프 파일이 IMPORT 할때 사용 | |
| -tables | IMPORT될 테이블 리스트 | |
| -commit | 배열(배열의 크기는 BUFFER에 의해 설정됩니다) 단위로 COMMIT을 할것인가 결정 | 기본적으로는 테이블 단위로 COMMIT |
| -fromuser | EXPORT덤프 파일로 부터 읽혀져야 하는 객체들을 갖고 있는 테이터베이스 계정 | |
| -touser | EXPORT덤프 안에 있는 객체들이 IMPORT될 데이터베이스 계정 |
Import 작업은 Export 에 비해 데이터베이스 상황에 따라서 에러 발생 소지가 굉장히 많은 작업이다. 주로 에러는 Export 보다Import 시 많이 발생한다.
에러의 패턴은 주로
- 익스포트할 사용자와 임포트할 사용자의 ID가 틀린 경우
- 익스포트한 객체들의 테이블 스페이스와 임포트할 테이블 스페이스 이름이 다른 경우
- 임포트할 테이블스페이스 스토리지 옵션과 덤프할 데이터가 맞지 않은 경우 ( Initial 사이즈가 너무 크거나 작다거나 등 )
의 경우 많이 발생한다.
1. 익스포트한 사용자와 임포트할 사용자의 ID가 틀린 경우
fromuser , touser 옵션을 사용하여 명시적으로 ID 를 지정한다.
예 ) imp 오라클사용자명/암호@인스턴스명 file=덤프파일이름 fromuser=익스포트한사용자ID touser=임포트할ID
2. 익스포트한 DB 와 임포트한 DB 의 테이블스페이스명이 틀린 경우
이 경우에는 여러가지 해결 방법이 있으나 보통 덤프 파일을 통해서 테이블 생성 스크립트를 만든 후 작업을 진행한다
* 덤프 파일에서 테이블 생성 스크립트 추출하기 : imp 명령의 indexfile 옵션을 이용한다
imp 오라클사용자명/암호@인스턴스명 file=덤프파일이름 fromuser=익스포트한사용자ID touser=임포트할ID indexfile=스크립트명
디렉토리에 스크립트가 생긴 것을 볼 수 있다. 이 생성 스크립트를 바로 사용할 수는 없으니 VI 등의 에디터로 편집한다.
에디터에서 보면 각 오브젝트들의 생성 스크립트가 생긴 것을 볼 수 있다. 이 생성 스크립트는 전부 주석 처리가 되어 있는데 (REM) 주석을 모두 제거한다. 그리고 생성 스크립트에서 TABLESPACE 이름을 임포트할 곳의 테이블 스크립트에 맞게 변경해준다. 그리고 각 테이블의 생성 스크립트 마지막 줄에는 … XXX Rows 라고 익스포트된 레코드를 표시하는 부분이 있는데 이 라인들도 모두 제거한다. 그리고 스크립트 처음 부분에 보면 첫번째 테이블을 생성하고 connect 명령어가 나오는데 이 부분 역시 제거하자.
생성 스크립트를 모두 수정하였으면 SQL/Plus에서 테이블생성스크립트를 돌린 후 imp 명령을 다시 실행한다.
이때 indexfile 옵션은 없애야 하며 대신 ignore=yes 옵션을 추가한다. ignore=yes 는 임포트시 동일한 이름의 오브젝트가 있는 경우 에러를 내고 임포트를 skip 하는데 이를 무시하고 임포트 할 수 있게 해준다.
imp 오라클사용자명/암호@인스턴스명 file=덤프파일이름 fromuser=익스포트한사용자ID touser=임포트할ID ignore=yes
* 테이블스페이스 스토리지 옵션에 따른 에러는 너무 다양한 에러와 처리 방법이 존재하므로 발생한 에러에 맞는 대응을
해야함
3. 기존의 자료가 이미 있는 경우 전체 테이블 drop script 만드는 법
SQL/Plus 의 Spool 명령어와 TABS 뷰를 이용하여 사용자의 모든 테이블을 드롭하는 스크립트를 생성한다
SQL/Plus 접속 후
spool drop.sql ( 파일 이름은 알아서.. ) select 'drop table '||table_name||';' from tabs; spool off
SQL/Plus 를 빠져나가서 보면 drop.sql 이 생성된 것을 볼 수 있다. 이 파일을 수정해서 사용하면 된다. 파일을 열어보면 아까 수행했던 SQL/Plus 화면이 그대로 저장된 것을 볼 수 있다. 이 파일에는 drop table .. 로 시작하는 SQL 구문과 그렇지 않은 부분이 있으므로 SQL 구문이 아닌 문장을 모두 제거하고 저장한 다음 SQL/PLUS 로 접속해서
@drop
명령을 실행한다 ( 명령은 아니고 drop.sql 을 실행하라는 SQL/Plus 명령이다. 파일명이 drop_table.sql 일 경우는 @drop_table 이런 식으로 실행한다 )
테이블이 일괄 드롭되나 몇개 테이블들은 Referencial Integrity 에러를 내면서 드롭되지 않는다. 이런 경우는 수동으로 drop 하면된다
@drop 으로 테이블을 드롭하였으면
select table_name from tabs;
문을 실행하여 남아있는 테이블들을 확인한 후
drop table 테이블명;
구문을 실행하여 모두 삭제한다.
테이블이 모두 drop 되면 위에서 언급한 imp 명령어를 실행해서 임포트 작업을 진행한다.
