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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
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
执行过程省略，太大..................................................................