Expdp 연습(datapump) ; tablespace export,import datapump 하기

1. koo tbs export 받음

[oracle@server15 ~]$ expdp system/oracle tablespaces=koo directory=dp dumpfile=koo_tbs.dmpdp

Export: Release 10.2.0.5.0 - Production on Thursday, 16 February, 2012 8:57:43

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01":  system/******** tablespaces=koo directory=dp dumpfile=koo_tbs.dmpdp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "KOO"."ME"                                  5.554 KB       2 rows
. . exported "KOO"."PROF"                                5.585 KB       2 rows
. . exported "KOO"."TEST"                                23.66 KB    1000 rows
. . exported "KOO"."YOU"                                 5.554 KB       2 rows
Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:
  /home/oracle/datapump/koo_tbs.dmpdp
Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at 08:58:14

2. 부어놓을 쪽 db자료상황 체크(반드시 koo tablespace가 만들어져있어야 합니다)

SQL> select OWNER, TABLE_NAME, TABLESPACE_NAME from dba_tables
  2  where TABLESPACE_NAME='KOO';

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
KOO                            ME                             KOO
KOO                            PROF                           KOO
(2개밖에 없습니다. 원래db에서는 4개의 table)

[oracle@server15 ~]$ impdp directory=dp dumpfile=koo_tbs.dmpdp full=y table_exists_action=truncate  <<실행

Import: Release 10.2.0.5.0 - Production on Thursday, 16 February, 2012 9:06:56

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

Username: system
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=dp dumpfile=koo_tbs.dmpdp full=y 

table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "KOO"."YOU" exists and has been truncated. Data will be loaded but all dependent metadata will be 

skipped due to table_exists_action of truncate
ORA-39153: Table "KOO"."TEST" exists and has been truncated. Data will be loaded but all dependent metadata will be 

skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "KOO"."ME"                                  5.554 KB       2 rows
. . imported "KOO"."PROF"                                5.585 KB       2 rows
. . imported "KOO"."TEST"                                23.66 KB    1000 rows
. . imported "KOO"."YOU"                                 5.554 KB       2 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 09:07:07

3. 부어놓은 db에서 자료들 확인

SQL> select OWNER, TABLE_NAME, TABLESPACE_NAME from dba_tables
  2  where TABLESPACE_NAME='KOO';

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
KOO                            ME                             KOO
KOO                            PROF                           KOO
KOO                            YOU                            KOO
KOO                            TEST                           KOO

SQL> select count(*) from koo.test;

  COUNT(*)
----------
      1000

** tablespace export, import할떄에는 system 계정으로 해야하고, import쪽에 해당 tablespace가 생성되어있어야합니다.
(아래 오류확인)
* 해당 tablespace가 만들어져만 있으면, 원본tablespace와 신db tablespace는 size 등등은 달라도 상관없습니다.

SQL> drop tablespace koo including contents and datafiles;

Tablespace dropped.

SQL> !
[oracle@server15 ~]$ impdp directory=dp dumpfile=koo_tbs.dmpdp full=y table_exists_action=truncate

Import: Release 10.2.0.5.0 - Production on Thursday, 16 February, 2012 9:15:33

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

Username: system
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=dp dumpfile=koo_tbs.dmpdp full=y table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE failed to create with error:
ORA-00959: tablespace 'KOO' does not exist
Failing sql is:
CREATE TABLE "KOO"."ME" ("NO" NUMBER, "ADDRESS" VARCHAR2(10 BYTE), "HABIT" VARCHAR2(20 BYTE)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "KOO"

ORA-39083: Object type TABLE failed to create with error:
ORA-00959: tablespace 'KOO' does not exist
Failing sql is:
CREATE TABLE "KOO"."YOU" ("NO" NUMBER, "ADDRESS" VARCHAR2(10 BYTE), "NICK" VARCHAR2(10 BYTE)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "KOO"

ORA-39083: Object type TABLE failed to create with error:
ORA-00959: tablespace 'KOO' does not exist
Failing sql is:
CREATE TABLE "KOO"."PROF" ("NO" NUMBER, "NAME" VARCHAR2(10 BYTE), "HEIGHT" NUMBER(3,0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "KOO"

ORA-39083: Object type TABLE failed to create with error:
ORA-00959: tablespace 'KOO' does not exist
Failing sql is:
CREATE TABLE "KOO"."TEST" ("NO" NUMBER, "NAME" VARCHAR2(10 BYTE) DEFAULT 'Olive') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "KOO"

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 4 error(s) at 09:15:41