━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 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='디렉토리명' 옵션은 사용하지 않아도 된다.
######### 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 단위로도 안된다. #### 결론은. 안된다.
"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
"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
"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
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에 지정
"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로 옮기는것도 가능.
// 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
"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 되는것으로 확인 되었다.
// 실행중 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
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의 권한을 시스템에서 그대로 부여하는 것이었다.
