IMPDP 應用
測試環境接【移動資料】data pump（中）EXPDP應用且是在同一庫中執行的操作，如果是匯入到不同的庫，則還需要建立目錄物件並授權。

實驗：
1) 匯入表emp且從scott使用者下remap到xxf使用者下：

--建立測試使用者

SYS@ORA11GR2>create user xxf identified by xxf ;

SYS@ORA11GR2>select USERNAME,ACCOUNT_STATUS from dba_users where username='XXF';

USERNAME ACCOUNT_STATUS

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

XXF OPEN

SYS@ORA11GR2>grant connect,resource to xxf;

Grant succeeded.

SYS@ORA11GR2>conn xxf/xxf

Connected.

--匯入表並將表對映到 xxf 賬戶下（remap對映）

[oracle@wang datadump]$ impdp system/oracle directory=dir_dp dumpfile=emp30.dmp remap_schema=scott:xxf

Import: Release 11.2.0.4.0 - Production on Sat Sep 24 11:18:25 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit 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=dir_dp dumpfile=emp30.dmp remap_schema=scott:xxf

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "XXF"."EMP" 8.25 KB 6 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

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

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

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

ORA-00942: table or view does not exist

Failing sql is:

ALTER TABLE "XXF"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "XXF"."DEPT" ("DEPTNO") ENABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Sat Sep 24 11:18:32 2016 elapsed 0 00:00:04

[oracle@wang datadump]$ exit

exit

——驗證：

XXF@ORA11GR2>select tname from tab;

TNAME

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

EMP

注在匯入的時候，會提示個錯誤，原因是emp表存在外來鍵的緣故，如果是單純到匯入emp表，那麼此錯誤可以忽略

2) 匯入 schema模式

--刪除 scott 使用者

SYS@ORA11GR2>drop user scott cascade;

User dropped.

--建立名為 scott 賬戶

SYS@ORA11GR2>create user scott identified by tiger;

User created.

SYS@ORA11GR2>grant connect,resource to scott;

Grant succeeded.

SYS@ORA11GR2>host

[oracle@wang datadump]$

--匯入scott使用者下的表，即使用者模式匯入：

[oracle@wang datadump]$ impdp system/oracle directory=dir_dp dumpfile=scott.dmp

Import: Release 11.2.0.4.0 - Production on Sat Sep 24 11:24:52 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit 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=dir_dp dumpfile=scott.dmp

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT"."DEPT" 5.929 KB 4 rows

. . imported "SCOTT"."EMP" 8.562 KB 14 rows

. . imported "SCOTT"."SALGRADE" 5.859 KB 5 rows

. . imported "SCOTT"."SL_BASE" 5.914 KB 3 rows

. . imported "SCOTT"."BONUS" 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/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sat Sep 24 11:24:56 2016 elapsed 0 00:00:03

[oracle@wang datadump]$ exit

exit

SYS@ORA11GR2>conn scott/tiger

Connected.

SCOTT@ORA11GR2>select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE

SL_BASE TABLE

3) 匯入表空間
--刪除 users 表空間下的所有表

SYS@ORA11GR2>select 'drop table '||OWNER||'.'||TABLE_NAME||' purge;' from dba_tables where tablespace_name='USERS';

'DROPTABLE'||OWNER||'.'||TABLE_NAME||'PURGE;'

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

drop table HR.FT purge;

drop table OE.PRODUCT_REF_LIST_NESTEDTAB purge;

drop table OE.SUBCATEGORY_REF_LIST_NESTEDTAB purge;

drop table XXF.EMP purge;

drop table SCOTT.DEPT purge;

drop table SCOTT.EMP purge;

drop table SCOTT.BONUS purge;

drop table SCOTT.SALGRADE purge;

drop table SCOTT.SL_BASE purge;

9 rows selected.

執行上述語句，刪除表空間users下的所有表；

——驗證：

SCOTT@ORA11GR2>select owner,table_name from dba_tables where tablespace_name='USERS';

select owner,table_name from dba_tables where tablespace_name='USERS'

*

ERROR at line 1:

ORA-00942: table or view does not exist

SYS@ORA11GR2>

我們發現， users 表空間的表都已經消失

SYS@ORA11GR2>conn scott/tiger

Connected.

SCOTT@ORA11GR2>select tname from tab;

no rows selected

SCOTT@ORA11GR2>select table_name,tablespace_name from user_tables;

no rows selected

--透過 impdp 匯入 users 表空間

[oracle@wang datadump]$ impdp system/oracle directory=dir_dp dumpfile=ts.dmp tablespaces=users

Import: Release 11.2.0.4.0 - Production on Sat Sep 24 11:44:51 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Starting "SYSTEM"."SYS_IMPORT_TABLESPACE_01": system/******** directory=dir_dp dumpfile=ts.dmp tablespaces=users

Processing object type TABLE_EXPORT/TABLE/TABLE

ORA-39151: Table "OE"."CATEGORIES_TAB" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "OE"."PURCHASEORDER" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "HR"."FT" 16.80 KB 107 rows

. . imported "SCOTT"."DEPT" 5.929 KB 4 rows

. . imported "SCOTT"."EMP" 8.562 KB 14 rows

. . imported "SCOTT"."SALGRADE" 5.859 KB 5 rows

. . imported "SCOTT"."SL_BASE" 5.914 KB 3 rows

. . imported "SCOTT"."BONUS" 0 KB 0 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

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

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_IMPORT_TABLESPACE_01" completed with 2 error(s) at Sat Sep 24 11:44:55 2016 elapsed 0 00:00:03

驗證：

SCOTT@ORA11GR2>select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME

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

DEPT USERS

EMP USERS

BONUS USERS

SALGRADE USERS

SL_BASE USERS

SCOTT@ORA11GR2>

4) 匯入資料庫

SCOTT@ORA11GR2>exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@wang datadump]$ impdp system/oracle directory=dir_dp dumpfile=db.dmp full=y
執行過程省略，太大..................................................................

【移動資料】data pump（下） IMPDP 應用

相關文章