오라클 expdp / impdp

datapump

- 오라클 10g 버전부터 생긴 유틸리티

- export, import의 느린 속도 및 작업시간 예측의 어려운 단점이 있어 datapump 를 만듬

- 오라클에 directory 라는 객체를 사용함.

- directory 가 생성되어 있어야 하며, 해당 user 가 그 directory 에 접근권한이 있어야 함.

 

*2015.10.31. 추가

- select * from dba_directories; --디렉토리가 어디생겼는지 못찾을때..

1. 디렉토리 생성 및 권한 주기 

ex) /data/EXP_DIR/라는 디렉토리가 있다고 가정하고 권한을 설정

SQL>create or replace directory EXP_DIR as '/data/EXP_DIR';

SQL> grant read,write on directory EXP_DIR to scott;

SQL> grant create any directory to scott;

2. expdp (콘솔에서 실행한다. sqlplus 아님)
 

expdp scott/tiger DIRECTORY=EXP_DIR DUMPFILE=scottdmp.dmp LOGFILE=scottdmp.log

scott user 의 데이터를 백업한다.

3. impdp

impdp scott/tiger DIRECTORY=EXP_DIR DUMPFILE=scottdmp.dmp REMAP_SCHEMA=scott:newscott

REMAP_DATAFILE='C:\user01.dbf':'/usr/data/user01.dbf' (데이터 파일 변경)

 

REMAP_TABLESPACE='xxx':'yyy' (테이블스페이스 변경)

scott user 로 접속하여 scott user 의 데이터를 새로운 newscott user 에 백업한다.

이때 newscott 는 새로 생성된다.

4.자세한 설명은 아래 사이트를 참고하세요.

datapump(expdp) :  http://blog.naver.com/saimoo?Redirect=Log&logNo=100143148086

datadump(impdp) 

이관 - expdp/impdp 명령어[출처] datadump(impdp)|작성자 oracleguy

 : http://blog.naver.com/saimoo?Redirect=Log&logNo=100143148086

 오라클 expdp/impdp Utility : http://hoonsik69.tistory.com/entry/%EC%98%A4%EB%9D%BC%ED%81%B4-expdpimpdp-Utility

이관 - expdp/impdp 명령어 : http://blog.naver.com/itisksc?Redirect=Log&logNo=30088616193

Backup & Recovery : expdp & impdp : 

http://blog.naver.com/PostView.nhn?blogId=kwoncharlie&logNo=10085313797&viewDate=&currentPage=1&listtype=0

http://blog.naver.com/PostView.nhn?blogId=conifer7&logNo=30096515627

http://j.finfra.com/zbl/lecOracle10gLecdata/8858

ORACLE_EXPDP http://blog.naver.com/amateras_2?Redirect=Log&logNo=140111579588

expdp, impdp 실습 :  http://cafe.naver.com/ocmkorea.cafe  

출처 : 네이버 카페 Pride of OCM ...& OCMKOREA

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
                                                    expdp, impdp 실습
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
expdp, impdp 실습은 이전 버전인 exp,imp와 비슷하기 때문에 (실제로 옵션명이 바뀐것들도 있음)
새로운 기능 몇가지들만 실습하겠다.

 

실습 준비 사항

SQL> create tablespace test datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST1.DBF' size 1m;

SQL> alter tablespace test add datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST2.DBF' size 1m;
SQL> alter tablespace test add datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST3.DBF' size 1m;

 

SQL> create user orcl identified by orcl
         default tablespace test;

 

SQL> grant connect, resource, select any table to orcl;
SQL> grant exp_full_database, imp_full_database to orcl;           // exp, imp database 사용시

SQL> create table orcl.test as select * from scott.emp;
SQL> create directory test as 'd:\test\';                               // 디렉토리 생성

SQL> grant read,write on directory test to orcl;                        // 생성된 디렉토리에 대한 권한

 

exp/imp 에서는 디렉토리 설정이 없었다. file='경로명\파일명' 으로 직접 경로만 지원 하였으나
datapup export/import에서는 디렉토리 설정을 한뒤,
해당 디렉토리에 대한 권한만 주면 directory='디렉토리명' 식으로 사용할수 있다.
또한 default directory 설정을 하게 되면 directory='디렉토리명' 옵션은 사용하지 않아도 된다.

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
                                  dumpfile expdp(full, tablespace, table, schema)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

######### database full mode
D:\test>expdp '/ as sysdba' dumpfile=test:a.dmp full=y

######### tablespace mode
D:\test>expdp '/ as sysdba' dumpfile=test:b.dmp tablespaces=test
D:\test>expdp '/ as sysdba' dumpfile=test:r.dmp tablespaces=test,users

######### table mode
D:\test>expdp '/ as sysdba' dumpfile=test:c.dmp tables=orcl.test
D:\test>expdp '/ as sysdba' dumpfile=test:w.dmp tables=orcl.test,orcl.test2
D:\test>expdp orcl/orcl dumpfile=test:c.dmp tables=test
// 위의 경우는 실행이 가능한 경우.

 

D:\test>expdp '/ as sysdba' dumpfile=test:c.dmp tables=test
// 이 명령은 expdp 실행의 주체가 sys이고 table은 orcl의 table이다.
// test table이라는 객체를 찾을수 없다는 메세지를 내보내며 실패한다.

######### schemas mode
D:\test>expdp '/ as sysdba' dumpfile=test:e.dmp schemas=orcl
D:\test>expdp '/ as sysdba' dumpfile=test:f.dmp schemas=orcl,orcl2,orcl3

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
                                            dumpfile 분할 expdp
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
conn orcl/orcl

 

SQL> select segment_name, tablespace_name, bytes/1024/1024 from user_segments;

 

SEGMENT_NAME         TABLESPACE_NAME                BYTES/1024/1024
-------------------- ------------------------------ ---------------
TEST3                TEST                                     .0625
TEST2                TEST                                     .0625
TEST                 TEST                                        22

 

// test table의 크기가 22M 로 확인 할수있다.

 

### 덤프 파일의 size가 2M가 되면 자동적으로 새로운 덤프파일에 번호를 부여.
D:\test>expdp '/ as sysdba' directory=test dumpfile=test%u.dmp tables=orcl.test filesize=2m

 

BLOCKS 메소드를 사용한 총 예측: 22 MB
객체 유형 TABLE_EXPORT/TABLE/TABLE 처리 중
. . "ORCL"."TEST"                               18.16 MB  458752행이 엑스포트됨
마스터 테이블 "SYS"."SYS_EXPORT_TABLE_01"이(가) 성공적으로 로드됨/로드 취소됨
******************************************************************************
SYS.SYS_EXPORT_TABLE_01에 대해 설정된 덤프 파일:
  D:\TEST\TEST01.DMP
  D:\TEST\TEST02.DMP
  D:\TEST\TEST03.DMP
  D:\TEST\TEST04.DMP
  D:\TEST\TEST05.DMP
  D:\TEST\TEST06.DMP
  D:\TEST\TEST07.DMP
  D:\TEST\TEST08.DMP
  D:\TEST\TEST09.DMP
  D:\TEST\TEST10.DMP
"SYS"."SYS_EXPORT_TABLE_01" 작업이 09:40:23에서 성공적으로 완료됨 명령.

 

// sql 상에서 table의 대략적인 size는 22M 였고 expdp시 예측 size도 22M 였으나
// 실제로는 18M 정도의 size가 나왔다.
// 이유는 expdp는 자동적으로 압축하는 기능이 있기때문이다.

 

#### impdp시 큰 변동없이 명령어를 사용하면 됨
D:\test>impdp '/ as sysdba' directory=test dumpfile=test%u.dmp tables=orcl.test

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
                      여러유저의 원하는 table만 한번의 명령으로 expdp 가능할까?
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
#### 결론은 가능하지 않다.

 

D:\test>expdp '/ as sysdba' dumpfile=test:user.dmp schemas=orcl2 include=table:\='ORCL.TEST'
// 위 명령은 스키마는 orcl2이고 include에서 user.table 이 먹히는지 해본것이다.
// 결과는 실패한다. include의 기본 문법에 user name은 들어가지 않는다.

 

D:\test>expdp '/ as sysdba' dumpfile=test:user.dmp schemas=orcl,orcl2 include=table:\='TEST' include=table:\='SS'
// 스키마에 유저 2명을 지정하고 include를 각각 선언해 보았으나. 어느 유저의 table인지 명확하지 않으므로
// 위 명령도 실패한다.

 

D:\test>expdp '/ as sysdba' dumpfile=test:user.dmp schemas=orcl2 include=table:\='test'
// orcl2 스키마를 expdp 하면서 orcl 유저의 test table만 include 하려 해도
// test table을 찾을수 없기에 실패한다.

 

D:\test>expdp '/ as sysdba' dumpfile=test:user.dmp schemas=orcl2.ss,orcl.test
// 스키마에 user.table 로 명령을 내리면 단순히 해당 유저의 모든 테이블이 expdp 된다.
// 결과는 orcl2 유저의 ss 테이블과 orcl 유저의 test1, test2, test3 테이블 모두 expdp 된다.

 

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
                          user가 존재할때 impdp시 경고 메세지 안보는법
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
#### 기본적으로 schema impdp 할떄 user를 새로 생성하기 때문에 
#### 유저가 있는 상태에서 impdp시 경고 메세지를 띄운다.
#### 이 메세지를 안보려면

D:\test>impdp '/ as sysdba' dumpfile=test:test.dmp exclude=user

 

#### exclude=user 옵션을 사용해서 user를 새로 생성하지 않게 할수있다.

 

 

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
              REMAPPING을 사용해 하나의 dump에서 여러 유저에게 impdp가 가능 할까??
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
D:\test>impdp '/ as sysdba' dumpfile=test:test2.dmp remap_tablespace='orcl.test:orcl2.test' 
             remap_schema='orcl.test2:oo.test2' remap_schema='orcl.test3:orcl2.test3'

D:\test>impdp '/ as sysdba' dumpfile=test:test2.dmp remap_schema='orcl.test3:orcl2.test' 
             remap_schema='orcl.test:oo.test2' remap_schema='orcl.test2:orcl2.test3'

 

#### 위의 두가지 모두 안된다.
#### 또한 datapump에서는 fromuser touser 개념이 없기 때문에 schema 또는 table 단위로도 안된다.
#### 결론은. 안된다.

 

 

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
                                   expdp/impdp EXCLUDE/INCLUDE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

##################### case 1) EXCLUDE

SQL> conn orcl/orcl

SQL> create table test2 as select * from test;

SQL> insert into test value(select * from test2);

 

SQL> select count(*) from test;

  COUNT(*)
----------
        14

 

SQL> select count(*) from test2;

  COUNT(*)
----------
        28

 

D:\test>expdp orcl/orcl directory=test dumpfile=user2.dmp logfile=user2.log schemas=orcl exclude=table:"\='TEST2'"

"ORCL"."SYS_EXPORT_SCHEMA_01" 작업이 15:23:35에서 성공적으로 완료됨

 

---- 다른 예들 
예 ) D:\test>expdp ~~~~~~~~~~ schemas=orcl exclude=table:\"in \('TEST2','TEST3'\)\"       //test2, test3를 제외한 export
예 ) D:\test>expdp ~~~~~~~~~~ schemas=orcl exclude=table:\"like 'TEST%'\"       // test로 시작하는 table을 제외한 export

SQL> drop table test;

테이블이 삭제되었습니다.

D:\test>impdp orcl/orcl directory=test dumpfile=user2.dmp schemas=orcl

"ORCL"."SYS_IMPORT_SCHEMA_01" 작업이 1 오류와 함께 15:34:46에서 완료됨

 

SQL> select count(*) from test;

  COUNT(*)
----------
        14

 

// exclude=table:"\='TEST2'" >>> exp/imp에서 \(escape) 문자의 사용처를 정리한 것이 있다. 확인!!

 

SQL> select segment_name, segment_type, tablespace_name
  2  from user_segments;

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
TEST3                          TABLE              TEST
TEST2                          TABLE              TEST
TEST                           TABLE              TEST
AA_TEST                        INDEX              TEST
ORCL                           TABLE              TEST

 

// 현재 export 된것은 test2 table을 제외한 전부가 export 되었다.

 

SQL> drop table test3;

SQL> drop table test2;

SQL> drop table test;

SQL> drop table orcl;

D:\test>impdp orcl/orcl directory=test dumpfile=user2.dmp schemas=orcl exclude=table:"\='ORCL'"

"ORCL"."SYS_IMPORT_SCHEMA_01" 작업이 1 오류와 함께 11:50:39에서 완료됨

SQL> select segment_name, segment_type, tablespace_name
         from user_segments;

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
AA_TEST                        INDEX              TEST
TEST3                          TABLE              TEST
TEST                           TABLE              TEST

 

// import 시 exclude 시킨 orcl table을 제외한 index 및 table들이 import 된것을 확인할수 있다.

 

#### index를 제외한 impdp

SQL> select segment_name, segment_type, tablespace_name
         from user_segments;

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
TEST                           TABLE              TEST
TEST3                          TABLE              TEST
AA_TEST                        INDEX              TEST2

 

D:\test>expdp orcl/orcl dumpfile=test:in.dmp schemas=orcl

"ORCL"."SYS_EXPORT_SCHEMA_01" 작업이 13:49:48에서 성공적으로 완료됨

 

SQL> drop index aa_test;
SQL> drop table test purge;
SQL> drop table test3 purge;

D:\test>impdp orcl/orcl directory=test dumpfile=in.dmp schemas=orcl exclude=index:\='AA_TEST'
"ORCL"."SYS_IMPORT_SCHEMA_01" 작업이 1 오류와 함께 12:47:04에서 완료됨

 

SQL> select segment_name, segment_type, tablespace_name
         from user_segments;

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
TEST3                          TABLE              TEST
TEST                           TABLE              TEST

 

// index만 제외 되고 import 된것을 확인 할수 있다.

 

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
##################### case 2) include

SQL> drop table test purge;
SQL> drop table test3 purge;

SQL> select segment_name, segment_type, tablespace_name
         from user_segments;

 

선택된 레코드가 없습니다.

D:\test>impdp orcl/orcl directory=test dumpfile=in.dmp schemas=orcl include=table:\='TEST'
"ORCL"."SYS_IMPORT_SCHEMA_01" 작업이 12:43:40에서 성공적으로 완료됨

 

SQL> select segment_name, segment_type, tablespace_name
         from user_segments;

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
AA_TEST                        INDEX              TEST
TEST                           TABLE              TEST

 

// 해당 table만 import 하여도 index는 자동적으로 import 된다.

 

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

#### table이 존재하지 않는데 index만 impdp가 될까???

 

D:\test>impdp orcl/orcl directory=test dumpfile=in.dmp schemas=orcl include=index:\='AA_TEST'

ORA-39083: 다음 오류와 함께 객체 유형 INDEX 생성 실패:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다
실패한 SQL:
CREATE INDEX "ORCL"."AA_TEST" ON "ORCL"."TEST" ("EMPNO") PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE
(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GR
OUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TEST" PARALLEL 1
객체 유형 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS 처리 중
ORA-39112: 기본 객체 유형 INDEX_STATISTICS 생성을 실패하여 종속 객체 유형 INDEX:"ORCL"."AA_TEST"을(
를) 건너 뜀
"ORCL"."SYS_IMPORT_SCHEMA_01" 작업이 2 오류와 함께 12:54:28에서 완료됨

 

// index만은 import 되지 않는것을 알수있다.
// test table이 존재하지 않기 때문에 인덱스만 import 될순 없다.

 

SQL> select segment_name, segment_type, tablespace_name
          from user_segments;

선택된 레코드가 없습니다.

 

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
#### table이 존재하는 상황에서 index만 impdp

 

SQL> select segment_name, segment_type, tablespace_name
         from user_segments;

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
AA_TEST                        INDEX              TEST
TEST3                          TABLE              TEST
TEST                           TABLE              TEST

 

SQL> drop index aa_test;

D:\test>impdp orcl/orcl directory=test dumpfile=in.dmp schemas=orcl include=index:\='AA_TEST'
"ORCL"."SYS_IMPORT_SCHEMA_01" 작업이 12:59:06에서 성공적으로 완료됨

 

SQL> select segment_name, segment_type, tablespace_name
         from user_segments;

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
AA_TEST                        INDEX              TEST
TEST3                          TABLE              TEST
TEST                           TABLE              TEST

 

// index만 삭제하고 index만 import 한 결과. index만 import 되는것을 확인 할수있다.
// 즉, index의 기본 table이 존재한다면 index만 import 할수있다.

 

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
#### table과 해당 table index를 서로 다른 tablespace에 지정

 

SQL> drop index aa_test;

 

D:\test>impdp orcl/orcl directory=test dumpfile=in.dmp schemas=orcl include=index:\='AA_TEST'            

             remap_tablespace='TEST:TEST2'

"ORCL"."SYS_IMPORT_SCHEMA_01" 작업이 13:06:02에서 성공적으로 완료됨

 

SQL> select segment_name, segment_type, tablespace_name
  2  from user_segments;

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
TEST3                          TABLE              TEST
TEST                           TABLE              TEST
AA_TEST                        INDEX              TEST2

 

// remap_tablespace로 index만 다른 tablespace로 옮기는것도 가능.

 

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
                            expdp/impdp SAMPLE , TABLE_EXISTS_ACTION
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

SQL> select count(*) from test;

  COUNT(*)
----------
        14

 

SQL> select count(*) from test2;

  COUNT(*)
----------
        28

 

// 현재 test table의 row는 총 14, test2 table의 row 수는 28이다. 
// sample 값을 30으로 주겠다. 이는 해당 table의 row의 30%만을 가져온다는 것이다.

 

D:\test>expdp orcl/orcl directory=test dumpfile=user3.dmp logfile=user3.log schemas=orcl sample=30
.
.
. . "ORCL"."TEST"                               7.414 KB       4행이 엑스포트됨     // 대략 28% 정도.
. . "ORCL"."TEST2"                              7.601 KB       9행이 엑스포트됨     // 대략 32% 정도.
.
.

// test table은 28%. test2 table은 32% 정도로 row를 export 하였다.
// 이는 sample=30으로 주었을때 30% 아래로 가져오는것이 아닌 30%에 근접하는 수치로 row를 export 한다는걸 알수 있다.

 

SQL> select rowid, ename from test;
ROWID              ENAME
------------------ ----------
AAAM+ZAAGAAAAB0AAA SMITH
AAAM+ZAAGAAAAB0AAB ALLEN
AAAM+ZAAGAAAAB0AAC WARD
AAAM+ZAAGAAAAB0AAD JONES
AAAM+ZAAGAAAAB0AAE MARTIN
AAAM+ZAAGAAAAB0AAF BLAKE
AAAM+ZAAGAAAAB0AAG CLARK
AAAM+ZAAGAAAAB0AAH SCOTT
AAAM+ZAAGAAAAB0AAI KING
AAAM+ZAAGAAAAB0AAJ TURNER
AAAM+ZAAGAAAAB0AAK ADAMS
AAAM+ZAAGAAAAB0AAL JAMES
AAAM+ZAAGAAAAB0AAM FORD
AAAM+ZAAGAAAAB0AAN MILLER

 

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
                                       impdp TABLE_EXISTS_ACTION
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
D:\test>impdp orcl/orcl directory=test dumpfile=user3.dmp table_exists_action=replace

"ORCL"."SYS_IMPORT_FULL_01" 작업이 1 오류와 함께 16:10:03에서 완료됨

// table_exists_action은 해당 테이블을 import 할때 같은 이름의 table을 만났을때 취하는 모션임.
// 여기서는 replace 옵션을 사용하여 기존 테이블을 drop 하고 재 생성 하였다.
// 1오류는 해당 dumpfile이 schema 단위 export이어서 유저가 존재하기 때문에 오류가 발생함.
// 자!! 그럼 어떤 data가 import 되었는지 확인해 보자.

 

SQL> select rowid, ename from test;

ROWID              ENAME
------------------ ----------
AAANAYAAGAAAAB0AAA SMITH
AAANAYAAGAAAAB0AAB MARTIN
AAANAYAAGAAAAB0AAC BLAKE
AAANAYAAGAAAAB0AAD SCOTT

 

SQL> select rowid, ename from test2;

ROWID              ENAME
------------------ ----------
AAANAXAAGAAAAAUAAA SMITH
AAANAXAAGAAAAAUAAB KING
AAANAXAAGAAAAAUAAC ADAMS
AAANAXAAGAAAAAUAAD MILLER
AAANAXAAGAAAAAUAAE SMITH
AAANAXAAGAAAAAUAAF BLAKE
AAANAXAAGAAAAAUAAG CLARK
AAANAXAAGAAAAAUAAH SCOTT
AAANAXAAGAAAAAUAAI JAMES

 

// ㅇㅇ? 뭔가 이상하지 않은가?? 30%의 데이터만 export 할때 대충 rowid 순서대로 export 될것이라 예측하였다.
// 근데 새로 import 된 data를 보면(ename을 통해서 보면) rowid 순서대로 export 된것이 아니라는것을 볼수있다.
// test2 table 까지 확인 한 걸과 rowid 순으로 export 되지는 않지만 rowid의 번호가 커지면서 무작위로
// export 되는것으로 확인 되었다.

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
                                  expdp/impdp ATTACH & JOB
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
D:\test>expdp orcl/orcl directory=test dumpfile=db.dmp full=y job_name=gee
.
.

// 실행중 CTRL+C를 누르면 
.
객체 유형 DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT 처리 중

Export>

// 상태가 된다.

 

Export> help                // help를 이용해서 옵션을 볼수 있다.

다음 명령은 대화식 모드에서 적합합니다.
참고: 약어도 허용됨

명령                                               설명
------------------------------------------------------------------------------
ADD_FILE                   덤프 파일 집합에 덤프 파일을 추가합니다.
CONTINUE_CLIENT      로깅 모드로 돌아옵니다. 유휴 상태인 경우 작업이 재시작됩니다.
EXIT_CLIENT              클라이언트 세션을 종료하고 작업은 실행되도록 둡니다.
FILESIZE                    이후의 ADD_FILE 명령에 대한 기본 파일 크기(바이트)입니다.
HELP                         대화형 명령을 요약합니다.
KILL_JOB                   작업을 분리하고 삭제합니다.
PARALLEL                 현재 작업에 대한 활성 작업자 수를 변경합니다.
                                PARALLEL=<작업자 수>.
START_JOB              현재 작업을 시작/재개합니다.
STATUS                    작업 상태가 모니터되는 빈도(초)입니다.
                               기본값(0)은 사용 가능할 때 새 상태를 표시합니다.
                               STATUS[=간격]
STOP_JOB              작업 실행을 순서대로 종료하고 클라이언트를 종료합니다.
                              STOP_JOB=IMMEDIATE는 데이터 펌프 작업의
                              데이터 펌프 작업.

 

Export> status

 

작업: GEE
  작업: EXPORT
  모드: FULL
  상태: EXECUTING
  처리된 바이트: 0
  현재 병렬도: 1
  작업 오류 수: 0
  덤프 파일: D:\TEST\DB.DMP
    기록된 바이트: 4,096

작업자 1 상태:
  상태: EXECUTING
  객체 스키마: SYSMAN
  객체 이름: MGMT_METRICS_RAW
  객체 유형: DATABASE_EXPORT/SCHEMA/TABLE/TABLE
  완료된 객체: 8
  작업자 병렬도: 1

SQL> select * from dba_datapump_jobs;             // dba_datapump_jobs로도 확인 가능하다.

OWNER_NAME JOB_NAME   OPERATION  JOB_MODE   STATE          DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
---------- ---------- ---------- ---------- ---------- ---------- ----------------- -----------------
ORCL       GEE        EXPORT     FULL       EXECUTING           1                 1                 3

 

D:\test>expdp orcl/orcl ATTACH=orcl.GEE          // interactive mode로 접속하면 status도 같이 보인다.

Export> stop_job
이 작업을 정지하겠습니까([예]/아니오): yes

D:\test>expdp orcl/orcl ATTACH=orcl.GEE          // interactive mode로 접속하면 중지된 status도 보인다.

Export> kill_job
이 작업을 정지하겠습니까([예]/아니오): yes

 

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
                                                 REMAPPING
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
##################### case 1) schema

SQL> conn orcl/orcl

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST                           TABLE
TEST2                          TABLE

 

D:\test>expdp orcl/orcl directory=test dumpfile=orcl schemas=orcl

D:\test>impdp '/ as sysdba' directory=test dumpfile=orcl.dmp remap_schema=orcl:hr

// hr 유저가 기존에 있기 때문에 오류 발생한다.(상관없다)

SQL> conn hr/hr

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
REGIONS                        TABLE
COUNTRIES                      TABLE
LOCATIONS                      TABLE
DEPARTMENTS                    TABLE
JOBS                           TABLE
EMPLOYEES                      TABLE
JOB_HISTORY                    TABLE
EMP_DETAILS_VIEW               VIEW
TEST2                          TABLE
TEST                           TABLE

10 개의 행이 선택되었습니다.

 

// remap_schema를 사용해 orcl의 test, test2 table이 hr의 table로 import 된것을 확인할수 있다.

 

##################### 이러한 형식은 안된다.

 

D:\test>impdp '/ as sysdba' dumpfile=test:user2.dmp remap_schema=ORCL.TEST:OO.TEST
// 위 명령어를 할 경우 orcl user에 전부 impdp 된다.

 

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
##################### remap 대상 유저가 없는 경우.

D:\test>impdp '/ as sysdba' directory=test dumpfile=orcl.dmp remap_schema=orcl:orcl5
// 위 명령을 하면 orcl5 유저가 자동으로 생성 되며 impdp 된다.

 

SQL> select username, password, default_tablespace
         from dba_users
         where username in ('ORCL','ORCL5');

 

USERNAME                       PASSWORD                       DEFAULT_TABLESPACE
------------------------------ ------------------------------ ------------------------------
ORCL5                          F632EB619B212C2A               TEST
ORCL                           F632EB619B212C2A               TEST

 

SQL> conn orcl5/orcl
ERROR:
ORA-01017: 사용자명/암호가 부적합, 로그온할 수 없습니다

 

// 확인해보면 암호화된 password 까지 똑같지만 접속 할수는 없다.
// orcl5 유저에 비밀번호를 바꿔 주면 접속 할수있다.

 

SQL> alter user orcl5 identified by orcl5

SQL> conn orcl5/orcl5

SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
ORCL5                          SELECT ANY TABLE                         NO
ORCL5                          UNLIMITED TABLESPACE                     NO

 

SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
ORCL5                          CONNECT                        NO  YES NO
ORCL5                          DBA                            NO  YES NO
ORCL5                          EXP_FULL_DATABASE              NO  YES NO
ORCL5                          IMP_FULL_DATABASE              NO  YES NO
ORCL5                          RESOURCE                       NO  YES NO

 

// 시스템 권한과 부여 받은 Role을 살펴보면 orcl5는 orcl 유저의 모든 권한과 Role을 그대로 가져왔다.
// orcl 유저의 시스템과 Role 권한을 살펴봐도 with admin 옵션은 전부 no 였다.
// 혹시? 하여서 impdp 할때 '/ as sysdba' 가 아닌 orcl/orcl로 하였으나 결과는 동일했다.
// 즉, orcl에게 상속 받거나 하는것이 아닌.. orcl의 권한을 시스템에서 그대로 부여하는 것이었다.

 

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

##################### case 2) tablespace

// 이전의 tablespace와 user를 모두 drop 후에 새로 생성.

SQL> create tablespace test datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST.DBF' size 3m;
SQL> create user orcl identified by orcl default tablespace test;

SQL> grant connect, resource, exp_full_database, imp_full_database to orcl;

SQL> create table orcl.test as select * from scott.emp;

// test table의 tablespace는 orcl의 default tablespace가 test tablespace이기 때문에
// test tablespace 내에 위치 하게 된다.

SQL> select table_name, tablespace_name
         from dba_tables
         where table_name like 'TEST%';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEST                           TEST

D:\test>expdp orcl/orcl directory=test dumpfile=ts.dmp logfile=ts.log tablespaces=test

 

// 같은 DB에서 test 하기 때문에 기존 test table을 drop.

SQL> drop table test;

SQL> create tablespace test2 datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST2.DBF' size 3m;

D:\test>impdp orcl/orcl directory=test dumpfile=ts.dmp tablespaces=test remap_tablespace='TEST:TEST2'

// expdp 당시의 user가 DB 없다면 해당 user의 table은 impdp 되지 않는다.
// user가 test2 tablespace에 속하지 않아도 impdp 되며 table만 test2 tablespace에 속하게 된다.

SQL> select table_name, tablespace_name from dba_tables
         where table_name='TEST';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEST                           TEST2

// remap_tablespace로 test table이 옮겨진것을 확인할수 있다.

 

SQL> drop table test;

 

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
##################### case 3) 다중 reamp tablespace

SQL> create table orcl.test as select * from scott.emp;

SQL> create index aa_test on test(empno)                      // index를 TEST2 tablespace 내에 생성
         tablespace TEST2;

 

SQL> select segment_name, segment_type, tablespace_name
         from dba_segments
         where segment_name like '%TEST%';

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
MGMT_DB_LATEST_HDM_FINDINGS    INDEX              SYSAUX
MGMT_DB_LATEST_HDM_FINDINGS    TABLE              SYSAUX
MGMT_LATEST_SQL                TABLE              SYSAUX
MGMT_LATEST                    TABLE              SYSAUX
TEST                           TABLE              TEST
AA_TEST                        INDEX              TEST2

D:\test>expdp orcl/orcl directory=test dumpfile=ts.dmp tablespaces=test

SQL> drop table test;

SQL> create tablespace test3 datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST3.DBF' size 3m;

D:\test>impdp '/ as sysdba' dumpfile=test:ts.dmp remap_tablespace='TEST:TEST3','TEST2:TEST3'

// orcl 유저의 drop을 안했기 때문에. 1 오류 발생임.. 큰 상관 없음.

SQL> select segment_name, segment_type, tablespace_name
         from dba_segments
         where segment_name like '%TEST%';

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
MGMT_DB_LATEST_HDM_FINDINGS    INDEX              SYSAUX
MGMT_DB_LATEST_HDM_FINDINGS    TABLE              SYSAUX
MGMT_LATEST_SQL                TABLE              SYSAUX
MGMT_LATEST                    TABLE              SYSAUX
AA_TEST                        INDEX              TEST3
TEST                           TABLE              TEST3

6 개의 행이 선택되었습니다.

##################### 이런식의 하나의 tablespace를 여러개의 tablespace로는 할수없다.
D:\test>impdp '/ as sysdba' dumpfile=test:ts.dmp remap_tablespace='TEST:TEST2','TEST:TEST3'

 

##################### 하지만 요런건 된다. 
D:\test>impdp '/ as sysdba' dumpfile=test:ts.dmp remap_tablespace='TES2:TEST','TEST3:TEST'

 

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
##################### case 4) datafile

// A 시스템에서 B라는 시스템으로 DB 전체를 옮길때 사용된다.
// A 시스템과 B 시스템의 디스크 경로가 달라 import 실패시 사용됨.
// export 된 pumpfile이 datafile의 정보를 포함한 경우만 해당됨.

 

D:\test>expdp '/ as sysdba' directory=test dumpfile=full.dmp full=y

// expdp로 database 전체를 full export 하고

SQL> drop tablespace test;
SQL> drop tablespace test2;
SQL> drop tablespace test3 including contents;

D:\test>notepad full.par
directory=test 
dumpfile=full.dmp 
remap_datafile="'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST.DBF':'D:\TEST\TEST.DBF'",
"'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST2.DBF':'D:\TEST\TEST2.DBF'",
"'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST3.DBF':'D:\TEST\TEST3.DBF'"

// 커맨드 라인에서 remap_datafile= 이후에 명렁어 사용은 정확히 모름

d:\test>impdp '/ as sysdba' parfile=full.par

// impdp 하게 되면 성공.
// 결과는 알아서 확인.