PUMP

▣ Data Pump 의 장점

• Fine-grained object and data selection
• Explicit specification of database version
• Parallel execution
• Estimation of the export job space consumption
• Network mode in a distributed environment
• Remapping capabilities during import
• Data sampling and metadata compression

   

▣ Data Pump Export/Import interface

• Command line
• Parameter file
• Interactive command line => 사용하지 말것
• Database control => EM

   

▣ Data Pump Export/Import modes

• Full => 주의 : shared pool 부족할 수 있다.
• Schema
• Table
• Tablespace => exp/imp 에서는 불가
• Transportable tablespace

   

▣ 사용예

[oracle@orcl ~]$ expdp help=y

[oracle@orcl ~]$ expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp      =>      export

[oracle@orcl ~]$ ls scott.*

scott.dmp

S SYS> drop user scott cascade;

S SYS> create user scott identified by tiger;

User created.

S SYS> grant dba to scott;

S SYS> grant create procedure to scott;

Grant succeeded.

[oracle@orcl ~]$ impdp help=y

[oracle@orcl ~]$ impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

   

[oracle@orcl ~]$ expdp scott/tiger parfile=p.par

[oracle@orcl ~]$ cat >> p.par<<EOF

> DIRECTORY=dmpdir dumpfile=scott2.dmp

> EOF

[oracle@orcl ~]$ expdp scott/tiger parfile=p.par

   

▣ REMAP_SCHEMA : 해당 유저에 존재하는 모든 객체를 다른 유저로 이동

[oracle@edrsr4p1 ~]$ expdp scott/tiger directory=dmpdir dumpfile=scott.dmp

[oracle@edrsr4p1 ~]$ impdp system/oracle directory=dmpdir dumpfile=scott.dmp remap_schema='SCOTT':'HR'

   

Import: Release 10.2.0.1.0 - Production on Sunday, 16 August, 2009 21:43:07

   

Copyright (c) 2003, 2005, Oracle. All rights reserved.

   

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dmpdir dumpfile=scott.dmp r hema=SCOTT:HR

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39083: Object type TABLE failed to create with error:

ORA-02264: name already used by an existing constraint

Failing sql is:

CREATE TABLE "HR"."COUNTRY" ("COUNTRY_ID" CHAR(2), "COUNTRY_NAME" VARCHAR2(40), CONSTRAINT "C C_ID_PK" PRIMARY KEY ("COUNTRY_ID") ENABLE) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS ANS 255 NOLOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINC FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EXAMPLE" PCTT

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "HR"."TM" 3.818 MB 100000 rows

. . imported "HR"."BIBLE" 2.692 MB 17761 rows

. . imported "HR"."PT":"PT_P4" 2.435 MB 150001 rows

. . imported "HR"."PT":"PT_P1" 1.615 MB 99999 rows

. . imported "HR"."PT":"PT_P2" 1.625 MB 100000 rows

. . imported "HR"."PT":"PT_P3" 1.625 MB 100000 rows

. . imported "HR"."PT2":"PT2_P1"."PT2_P1_S1" 1.807 MB 100001 rows

. . imported "HR"."DEPT" 5.656 KB 4 rows

. . imported "HR"."EMP" 7.820 KB 14 rows

. . imported "HR"."SALGRADE" 5.585 KB 5 rows

. . imported "HR"."BONUS" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P1"."PT2_P1_S2" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P1"."PT2_P1_S3" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P1"."PT2_P1_S4" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P2"."PT2_P2_S1" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P2"."PT2_P2_S2" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P2"."PT2_P2_S3" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P2"."PT2_P2_S4" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P3"."PT2_P3_S1" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P3"."PT2_P3_S2" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P3"."PT2_P3_S3" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P3"."PT2_P3_S4" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P4"."PT2_P4_S1" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P4"."PT2_P4_S2" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P4"."PT2_P4_S3" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P4"."PT2_P4_S4" 0 KB 0 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 21:43:19

   

▣ 이동된 객체 확인

SQL> show user

USER is "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

EMPDEPT CLUSTER

EMPDEPT2 CLUSTER

EMPX TABLE 1

   

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

DEPTX TABLE 2

PT TABLE

PT2 TABLE

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

TM TABLE

BIBLE TABLE

   

20 rows selected.