#25_140717_WSHOP_DATA PUMP

# DATA PUMP

l data pump 를 사용하는 이유?

- 특정 테이블을 물리적 파일로 생성해서 백업을 하고 싶을 때

- truncate 같은 경우는 rollback, flashback 둘다 불가능한데 이럴 때 import를 이용한다

l Data pump의 모드 4가지?

1.     Table mode

2.     schema mode

- SCOTT 유저가 가지고 있는 모든 테이블들을 전부 export 받고 싶을 때

3.     tablespace mode

4.     database mode

- export pump : DB내의 테이블을 물리적 덤프 파일로 생성

- import pump : 물리적 덤프파일을 DB에 테이블로 생성

l export와 import : 9i 버전까지 사용

l export pump 와 import pump : 10g 버전부터 사용

l 위의 둘의 큰 차이점

1.     pump가 속도가 빠르다

2.     pump에 다양한 옵션이 추가 됐다

- data를 sampling 해서 export 받을 수 있다

- 압출해서 export를 받을 수 있다

- 병렬로 작업이 가능하다

- where 절을 사용해서 특정 조건의 데이터만 export 받을 수 있따

- 암호화를 할 수 있다.

- 작업을 중간에 중지시켰다 다시 이어서 시작

l pump file 이 생성될 디렉토리에(data_pump_dir) access 할 수 있는 권한을 부여

SQL> grant all on directory data_pump_dir to scott;

l pump 디렉토리 위치 확인

SQL> SELECT * FROM DBA_DIRECTORIES;

DATA_PUMP_DIR : /u01/app/oracle/admin/dbdb/dpdump/

n TABLE MODE

[dbdb:~]$ exp scott/tiger file=emp.dmp tables=emp Export: Release 11.2.0.1.0 - Production on Thu Jul 17 14:28:16 2014 ………………………………………………………………………………………. About to export specified tables via Conventional Path ... . . exporting table                            EMP         14 rows exported - emp 테이블의 14개 행이 export 되었다는 것을 확인 Export terminated successfully without warnings. SQL> drop table emp purge; [dbdb:~]$ imp scott/tiger file=emp.dmp tables=emp Import: Release 11.2.0.1.0 - Production on Thu Jul 17 14:37:07 2014 ………………………………………………………………………………………. . importing SCOTT's objects into SCOTT . . importing table                          "EMP"         14 rows imported Import terminated successfully without warnings.

  

문제155. dept과 salgrade 테이블을 drop 하고 export 받으시오

[dbdb:~]$ exp scott/tiger file=DeptSalgrade.dmp tables=dept, salgrade

- 따로따로 해도 가능하고 위처럼 한번에 명령어를 입력해도 가능하다

[dbdb:~]$ exp scott/tiger file=dept.dmp tables=dept Export: Release 11.2.0.1.0 - Production on Thu Jul 17 14:40:35 2014 ………………………………………………………………………………………. About to export specified tables via Conventional Path ... . . exporting table                           DEPT          4 rows exported Export terminated successfully without warnings. [dbdb:~]$ exp scott/tiger file=salgrade.dmp tables=salgrade Export: Release 11.2.0.1.0 - Production on Thu Jul 17 14:40:53 2014 ………………………………………………………………………………………. About to export specified tables via Conventional Path ... . . exporting table                       SALGRADE          5 rows exported Export terminated successfully without warnings.

문제156. dept 테이블을 drop 하고 복구하시오

SQL> drop table dept; SQL> exit [dbdb:~]$ imp scott/tiger file=dept.dmp tables=dept Import: Release 11.2.0.1.0 - Production on Thu Jul 17 14:55:30 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. ………………………………………………………………………………………………………………….. . . importing table                         "DEPT"          4 rows imported Import terminated successfully without warnings. SQL> select * from dept;     DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK         20 RESEARCH       DALLAS         30 SALES          CHICAGO         40 OPERATIONS     BOSTON

n Schema mode

[dbdb:~]$ exp scott/tiger file=scott.dmp owner=scott ………………………………………………………………………………………. . about to export SCOTT's tables via Conventional Path ... . . exporting table                           DEPT          4 rows exported . . exporting table                            EMP         14 rows exported . . exporting table                       SALGRADE          5 rows exported . . exporting table                   SYS_TEMP_FBT ……………………………………………………………………………………….

문제157. salgrade 테이블을 drop 한 후 방금 export 한 scott.dmp로 salgrade 테이블을 복구하시오

[dbdb:~]$ imp scott/tiger file=scott.dmp tables=salgrade ………………………………………………………………………………………. . . importing table                     "SALGRADE"          5 rows imported ………………………………………………………………………………………. [dbdb:~]$ sqlplus scott/tiger SQL> select * from salgrade;      GRADE      LOSAL      HISAL ---------- ---------- ----------          1        700       1200          2       1201       1400          3       1401       2000          4       2001       3000          5       3001       9999

n EXPORT DUMP

[dbdb:~]$ expdp scott/tiger dumpfile=emp.dmp tables=emp Export: Release 11.2.0.1.0 - Production on Thu Jul 17 14:20:08 2014 ………………………………………………………………………………………. Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:   /u01/app/oracle/admin/dbdb/dpdump/emp.dmp Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:20:41

문제158. emp 테이블을 drop 하고 pump로 export 받은 파일로 복구하시오

[dbdb:~]$ impdp scott/tiger file=emp.dmp tables=emp . . imported "SCOTT"."EMP"                               8.570 KB      14 rows Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 15:38:08

문제159. emp 테이블을 truncate 시키고 복구시키시오

[dbdb:~]$ impdp scott/tiger file=emp.dmp tables=emp Import: Release 11.2.0.1.0 - Production on Thu Jul 17 15:41:37 2014 ………………………………………………………………………………………. ORA-39151: Table "SCOTT"."EMP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip Processing object type TABLE_EXPORT/TABLE/TABLE_DATA - 기존에 테이블이 존재하기 떄문에 오류가 발생한다 Job "SCOTT"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 15:41:40

[dbdb:~]$ impdp scott/tiger file=emp.dmp tables=emp TABLE_EXISTS_ACTION=append Import: Release 11.2.0.1.0 - Production on Thu Jul 17 15:44:50 2014 ………………………………………………………………………………………. ORA-39152: Table "SCOTT"."EMP" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "SCOTT"."EMP"                               8.570 KB      14 rows - 오류가 발생하지만 데이터 입력은 가능하다 Job "SCOTT"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 15:44:54

문제160. emp 테이블을 export 시키는데 월급이 1000 이상인 사원들의 data만 export 시키시오

[dbdb:~]$ expdp scott/tiger dumpfile=emp3.dmp QUERY=scott.emp:'where sal>1000' TABLES=emp Export: Release 11.2.0.1.0 - Production on Thu Jul 17 15:52:34 2014 ………………………………………………………………………………………. Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:   /u01/app/oracle/admin/dbdb/dpdump/emp3.dmp Job "SCOTT"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 15:52:55

문제161. smith 라는 유저를 생성하고 dba 권한을 부여하고 SCOTT 유저의 emp 테이블을 smith 유저에게 import 하시오

# SYS 계정에서

SQL> create user smith

    identified by tiger;

SQL> grant dba to smith;

[dbdb:~]$ impdp scott/tiger dumpfile=emp.dmp tables=emp remap_schema=SCOTT:SMITH Import: Release 11.2.0.1.0 - Production on Thu Jul 17 16:08:49 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** dumpfile=emp.dmp tables=emp remap_schema=SCOTT:SMITH Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "SMITH"."EMP"                               8.570 KB      14 rows Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 16:08:53