簡(jiǎn)單常用的數(shù)據(jù)泵導(dǎo)出導(dǎo)入(expdp/impdp)命令舉例(下)

目的：指導(dǎo)項(xiàng)目側(cè)自行進(jìn)行簡(jiǎn)單的數(shù)據(jù)泵遷移工作。

本文實(shí)驗(yàn)環(huán)境：Oracle 11.2.0.4欣舵，利用數(shù)據(jù)庫(kù)自帶的scott示例用戶進(jìn)行試驗(yàn)測(cè)試。
根據(jù)《簡(jiǎn)單常用的數(shù)據(jù)泵導(dǎo)出導(dǎo)入(expdp/impdp)命令舉例(上)》的expdp導(dǎo)出的二進(jìn)制文件進(jìn)行impdp導(dǎo)入缀磕。

1.首先需要?jiǎng)?chuàng)建Directory

2.創(chuàng)建表空間和用戶及賦權(quán)

3. 使用impdp導(dǎo)入用戶數(shù)據(jù)

3.1 導(dǎo)入scott用戶的元數(shù)據(jù)缘圈，且不包含統(tǒng)計(jì)信息；
3.2 導(dǎo)入scott用戶的數(shù)據(jù)袜蚕；
3.3 導(dǎo)入scott用戶下的emp糟把，dept表及數(shù)據(jù)；
3.4 導(dǎo)入scott用戶下的emp牲剃，dept表結(jié)構(gòu)遣疯；
3.5 導(dǎo)入scott用戶下所有的內(nèi)容；
3.6 并行導(dǎo)入scott用戶下所有的內(nèi)容凿傅；

4. 特殊需求

4.1 如果導(dǎo)入環(huán)境的用戶不同缠犀；
4.2 如果導(dǎo)入環(huán)境的表空間也不同；

由于我這里的實(shí)驗(yàn)環(huán)境還是導(dǎo)出的那臺(tái)主機(jī)狭归，所以需要先模擬出一個(gè)導(dǎo)入的環(huán)境夭坪，你實(shí)際導(dǎo)入并不需要。

1.刪除scott用戶过椎；
drop user scott cascade;
2.刪除users表空間。
drop tablespace users including contents and datafiles;
可能users表空間是默認(rèn)的數(shù)據(jù)庫(kù)表空間戏仓，導(dǎo)致刪除失敗疚宇，只需要更改下再執(zhí)行刪除即可亡鼠。

select a.property_name, a.property_value from database_properties a where a.property_name like '%DEFAULT%';
alter database default tablespace DBS_D_XXX;

這樣就模擬出了一個(gè)沒(méi)有users表空間和scott用戶的嶄新環(huán)境。

1. 首先需要?jiǎng)?chuàng)建Directory

這里目錄名字定義為"jy"敷待，
若是windows平臺(tái)间涵，對(duì)應(yīng)系統(tǒng)目錄為"E:\jingyu";

create or replace directory jy as 'E:\jingyu';

若是Unix/Linux平臺(tái)，對(duì)應(yīng)系統(tǒng)目錄為"/tmp/jingyu".

create or replace directory jy as '/tmp/jingyu';

注意：目錄在系統(tǒng)上需要真實(shí)存在（mkdir -p /tmp/jingyu）榜揖，且有訪問(wèn)的權(quán)限勾哩。
drwxr-xr-x. 2 oracle oinstall 4.0K May 22 16:48 jingyu

2. 創(chuàng)建表空間和用戶及賦權(quán)

create tablespace users datafile '+data1' size 10M autoextend on maxsize 30G;
create user scott identified by tiger default tablespace users;
grant connect, resource to scott;
grant read, write on directory jy to scott;

3. 使用impdp導(dǎo)入用戶數(shù)據(jù)

初始化環(huán)境：
初始1：得到刪除當(dāng)前用戶下表的SQL：select 'drop table '||table_name||' purge;' from user_tables;
初始2：得到查詢當(dāng)前用戶下表的數(shù)據(jù)量：select 'select count(1) from '||table_name||';' from user_tables;

3.1 導(dǎo)入scott用戶的元數(shù)據(jù)，且不包含統(tǒng)計(jì)信息举哟；

impdp system directory=jy dumpfile=scott_meta.dmp logfile=impdp_scott_meta.log

$ impdp system directory=jy dumpfile=scott_meta.dmp logfile=impdp_scott_meta.log

Import: Release 11.2.0.4.0 - Production on Tue May 26 13:36:41 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, 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=jy dumpfile=scott_meta.dmp logfile=impdp_scott_meta.log 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Tue May 26 13:36:49 2015 elapsed 0 00:00:03

3.2 導(dǎo)入scott用戶的數(shù)據(jù)思劳；

在3.1導(dǎo)入元數(shù)據(jù)后才可以導(dǎo)入數(shù)據(jù)。
impdp system directory=jy dumpfile=scott_data.dmp logfile=impdp_scott_data.log

$ impdp system directory=jy dumpfile=scott_data.dmp logfile=impdp_scott_data.log

Import: Release 11.2.0.4.0 - Production on Tue May 26 13:39:15 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, 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=jy dumpfile=scott_data.dmp logfile=impdp_scott_data.log 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . imported "SCOTT"."EMP"                               8.484 KB      12 rows
. . imported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . imported "SCOTT"."BONUS"                                 0 KB       0 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Tue May 26 13:39:26 2015 elapsed 0 00:00:07

3.3 只導(dǎo)入scott用戶下的emp表及數(shù)據(jù)妨猩；

這里為了演示導(dǎo)入潜叛，先初始化刪除scott用戶下的所有表。

impdp scott directory=jy tables=emp dumpfile=scott_emp_dept.dmp logfile=impdp_scott_emp.log

$ impdp scott directory=jy tables=emp dumpfile=scott_emp_dept.dmp logfile=impdp_scott_emp.log

Import: Release 11.2.0.4.0 - Production on Tue May 26 13:50:51 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, 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/******** directory=jy tables=emp dumpfile=scott_emp_dept.dmp logfile=impdp_scott_emp.log 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMP"                               8.484 KB      12 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Tue May 26 13:50:59 2015 elapsed 0 00:00:03

3.4 只導(dǎo)入scott用戶下的emp,dept表結(jié)構(gòu)壶硅；

這里為了演示導(dǎo)入威兜，先初始化刪除scott用戶下的所有表。

impdp scott directory=jy tables=emp,dept dumpfile=scott_emp_dept_meta.dmp logfile=impdp_scott_emp_dept_meta.log

$ impdp scott directory=jy tables=emp,dept dumpfile=scott_emp_dept_meta.dmp logfile=impdp_scott_emp_dept_meta.log

Import: Release 11.2.0.4.0 - Production on Tue May 26 13:54:38 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, 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/******** directory=jy tables=emp,dept dumpfile=scott_emp_dept_meta.dmp logfile=impdp_scott_emp_dept_meta.log 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Tue May 26 13:54:45 2015 elapsed 0 00:00:03

由于導(dǎo)出就是emp,dept兩張表庐椒，所以也可以不指定tables椒舵，以下兩種寫(xiě)法在這里都是可以的：
impdp scott directory=jy dumpfile=scott_emp_dept_meta.dmp logfile=impdp_scott_emp_dept_meta.log

impdp scott directory=jy dumpfile=scott_emp_dept_meta.dmp logfile=impdp_scott_emp_dept_meta.log full=y

3.5 導(dǎo)入scott用戶下所有的內(nèi)容；

impdp system directory=jy schemas=scott dumpfile=scott_all.dmp logfile=impdp_scott_all.log

如果是在2.4基礎(chǔ)上直接導(dǎo)入约谈，會(huì)因?yàn)閑mp,dept表已經(jīng)存在導(dǎo)致導(dǎo)入過(guò)程中會(huì)由于table_exists_action參數(shù)的默認(rèn)選項(xiàng)是skip逮栅，從而跳過(guò)emp，dept表數(shù)據(jù)的導(dǎo)入窗宇，如下：

$ impdp system directory=jy schemas=scott dumpfile=scott_all.dmp logfile=impdp_scott_all.log

Import: Release 11.2.0.4.0 - Production on Tue May 26 14:22:50 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=jy schemas=scott dumpfile=scott_all.dmp logfile=impdp_scott_all.log 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39151: Table "SCOTT"."DEPT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."EMP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . imported "SCOTT"."BONUS"                                 0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 3 error(s) at Tue May 26 14:22:57 2015 elapsed 0 00:00:03

所以這時(shí)我們想導(dǎo)入這些數(shù)據(jù)措伐，可以加參數(shù) table_exists_action，指定想要的選項(xiàng)军俊。
TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

這里選擇truncate侥加，即如果表存在，那么處理方式是truncate此表后導(dǎo)入文件中包含的數(shù)據(jù)粪躬。
impdp system directory=jy schemas=scott table_exists_action=truncate dumpfile=scott_all.dmp logfile=impdp_scott_all.log

$ impdp system directory=jy schemas=scott table_exists_action=truncate dumpfile=scott_all.dmp logfile=impdp_scott_all.log

Import: Release 11.2.0.4.0 - Production on Tue May 26 14:26:09 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=jy schemas=scott table_exists_action=truncate dumpfile=scott_all.dmp logfile=impdp_scott_all.log 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "SCOTT"."DEPT" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Table "SCOTT"."BONUS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Table "SCOTT"."SALGRADE" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Table "SCOTT"."EMP" 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 SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . imported "SCOTT"."EMP"                               8.484 KB      12 rows
. . imported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . imported "SCOTT"."BONUS"                                 0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Tue May 26 14:26:17 2015 elapsed 0 00:00:04

注意：如果這里選用append選項(xiàng)担败，那么如果原表有數(shù)據(jù)，且沒(méi)有合理的約束條件镰官，則可能導(dǎo)致數(shù)據(jù)的重復(fù)導(dǎo)入提前，所以，生產(chǎn)環(huán)境實(shí)際導(dǎo)入過(guò)程中一定要弄清楚數(shù)據(jù)的實(shí)際情況才能準(zhǔn)確決定如何選用此參數(shù)的選項(xiàng)泳唠。
如下所示狈网，SALGRADE表會(huì)出現(xiàn)5條重復(fù)數(shù)據(jù)：
impdp system directory=jy schemas=scott table_exists_action=append dumpfile=scott_all.dmp logfile=impdp_scott_all.log

$ impdp system directory=jy schemas=scott table_exists_action=append dumpfile=scott_all.dmp logfile=impdp_scott_all.log

Import: Release 11.2.0.4.0 - Production on Tue May 26 14:28:27 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=jy schemas=scott table_exists_action=append dumpfile=scott_all.dmp logfile=impdp_scott_all.log 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "SCOTT"."DEPT" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "SCOTT"."BONUS" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "SCOTT"."SALGRADE" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
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 SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "SCOTT"."DEPT" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated
ORA-31693: Table data object "SCOTT"."EMP" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (SCOTT.BIN$ESfmGQ7ZSsLgU58JqMBQqw==$0) violated
. . imported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . imported "SCOTT"."BONUS"                                 0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 3 error(s) at Tue May 26 14:28:34 2015 elapsed 0 00:00:03

3.6 并行導(dǎo)入scott用戶下所有的內(nèi)容；

impdp system directory=jy schemas=scott table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2

這里對(duì)于已經(jīng)存在的對(duì)象直接replace掉。

$ impdp system directory=jy schemas=scott table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2

Import: Release 11.2.0.4.0 - Production on Tue May 26 14:15:37 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=jy schemas=scott table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
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.484 KB      12 rows
. . imported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . imported "SCOTT"."BONUS"                                 0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Tue May 26 14:15:46 2015 elapsed 0 00:00:05

4. 特殊需求

特殊需求環(huán)境準(zhǔn)備：
創(chuàng)建表空間users2：
create tablespace users2 datafile '+data1' size 10M autoextend on maxsize 30G;
創(chuàng)建用戶scott2：
create user scott2 identified by tiger default tablespace users2;
賦權(quán)用戶scott2：
grant connect, resource to scott2;

4.1 如果導(dǎo)入環(huán)境的用戶不同拓哺；

需求：將原scott用戶的數(shù)據(jù)導(dǎo)入到現(xiàn)在的scott2用戶勇垛。
impdp system directory=jy schemas=scott REMAP_SCHEMA=scott:scott2 table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2

$ impdp system directory=jy schemas=scott REMAP_SCHEMA=scott:scott2 table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2

Import: Release 11.2.0.4.0 - Production on Tue May 26 14:55:02 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=jy schemas=scott REMAP_SCHEMA=scott:scott2 table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT2" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
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 "SCOTT2"."DEPT"                             5.929 KB       4 rows
. . imported "SCOTT2"."EMP"                              8.484 KB      12 rows
. . imported "SCOTT2"."SALGRADE"                         5.859 KB       5 rows
. . imported "SCOTT2"."BONUS"                                0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Tue May 26 14:55:09 2015 elapsed 0 00:00:04

4.2 如果導(dǎo)入環(huán)境的表空間也不同；

需求：將原users表空間的對(duì)象重定向到users2表空間士鸥。
impdp system directory=jy schemas=scott REMAP_SCHEMA=scott:scott2 REMAP_TABLESPACE=users:users2 table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2

$ impdp system directory=jy schemas=scott REMAP_SCHEMA=scott:scott2 REMAP_TABLESPACE=users:users2 table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2

Import: Release 11.2.0.4.0 - Production on Tue May 26 14:57:20 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=jy schemas=scott REMAP_SCHEMA=scott:scott2 REMAP_TABLESPACE=users:users2 table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT2" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
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 "SCOTT2"."DEPT"                             5.929 KB       4 rows
. . imported "SCOTT2"."EMP"                              8.484 KB      12 rows
. . imported "SCOTT2"."SALGRADE"                         5.859 KB       5 rows
. . imported "SCOTT2"."BONUS"                                0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Tue May 26 14:57:29 2015 elapsed 0 00:00:05

細(xì)心的朋友闲孤，會(huì)發(fā)現(xiàn)導(dǎo)入的日志最后都提示有一個(gè)錯(cuò)誤，往上查發(fā)現(xiàn)是報(bào)錯(cuò)ORA-31684用戶已存在烤礁，這是因?yàn)槲覀兞?xí)慣在導(dǎo)入前建立好對(duì)應(yīng)的用戶讼积，避免一些其他的權(quán)限錯(cuò)誤，所以這個(gè)錯(cuò)誤是可以忽略的脚仔。
當(dāng)然其實(shí)如果我們已經(jīng)建立了對(duì)應(yīng)的表空間勤众，用戶也是可以不事先建立的，比如：
我們這里的情景玻侥，如果只事先建立users2表空間决摧，不建立scott2用戶，也是可以成功導(dǎo)入且不會(huì)有任何報(bào)錯(cuò)提示凑兰。

$ impdp system directory=jy schemas=scott REMAP_SCHEMA=scott:scott2 REMAP_TABLESPACE=users:users2 table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2

Import: Release 11.2.0.4.0 - Production on Tue May 26 15:03:33 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=jy schemas=scott REMAP_SCHEMA=scott:scott2 REMAP_TABLESPACE=users:users2 table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
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 "SCOTT2"."DEPT"                             5.929 KB       4 rows
. . imported "SCOTT2"."EMP"                              8.484 KB      12 rows
. . imported "SCOTT2"."SALGRADE"                         5.859 KB       5 rows
. . imported "SCOTT2"."BONUS"                                0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Tue May 26 15:03:42 2015 elapsed 0 00:00:05

AlfredZhao?版權(quán)所有「從Oracle起航掌桩，領(lǐng)略精彩的IT技術(shù)」檬常」

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者

人面猴
序言：七十年代末波岛，一起剝皮案震驚了整個(gè)濱河市，隨后出現(xiàn)的幾起案子音半，更是在濱河造成了極大的恐慌则拷，老刑警劉巖，帶你破解...
沈念sama閱讀 219,589評(píng)論 6贊 508
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件曹鸠，死亡現(xiàn)場(chǎng)離奇詭異煌茬，居然都是意外死亡，警方通過(guò)查閱死者的電腦和手機(jī)彻桃，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 93,615評(píng)論 3贊 396
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門(mén)坛善，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)，“玉大人邻眷，你說(shuō)我怎么就攤上這事眠屎。” “怎么了肆饶？”我有些...
開(kāi)封第一講書(shū)人閱讀 165,933評(píng)論 0贊 356
道士緝兇錄：失蹤的賣(mài)姜人
文/不壞的土叔我叫張陵改衩，是天一觀的道長(zhǎng)。經(jīng)常有香客問(wèn)我驯镊，道長(zhǎng)葫督，這世上最難降的妖魔是什么竭鞍？我笑而不...
開(kāi)封第一講書(shū)人閱讀 58,976評(píng)論 1贊 295
?港島之戀（遺憾婚禮）
正文為了忘掉前任，我火速辦了婚禮候衍，結(jié)果婚禮上笼蛛，老公的妹妹穿的比我還像新娘洒放。我一直安慰自己蛉鹿，他們只是感情好，可當(dāng)我...
茶點(diǎn)故事閱讀 67,999評(píng)論 6贊 393
惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
文/花漫我一把揭開(kāi)白布往湿。她就那樣靜靜地躺著妖异，像睡著了一般。火紅的嫁衣襯著肌膚如雪领追。梳的紋絲不亂的頭發(fā)上他膳，一...
開(kāi)封第一講書(shū)人閱讀 51,775評(píng)論 1贊 307
城市分裂傳說(shuō)
那天，我揣著相機(jī)與錄音绒窑，去河邊找鬼棕孙。笑死，一個(gè)胖子當(dāng)著我的面吹牛些膨，可吹牛的內(nèi)容都是我干的蟀俊。我是一名探鬼主播，決...
沈念sama閱讀 40,474評(píng)論 3贊 420
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開(kāi)眼订雾，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼肢预！你這毒婦竟也來(lái)了？” 一聲冷哼從身側(cè)響起洼哎，我...
開(kāi)封第一講書(shū)人閱讀 39,359評(píng)論 0贊 276
萬(wàn)榮殺人案實(shí)錄
序言：老撾萬(wàn)榮一對(duì)情侶失蹤烫映，失蹤者是張志新（化名）和其女友劉穎，沒(méi)想到半個(gè)月后噩峦，有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體锭沟，經(jīng)...
沈念sama閱讀 45,854評(píng)論 1贊 317
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 38,007評(píng)論 3贊 338
?白月光啟示錄
正文我和宋清朗相戀三年识补，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了族淮。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
茶點(diǎn)故事閱讀 40,146評(píng)論 1贊 351
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡李请，死狀恐怖瞧筛，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情导盅，我是刑警寧澤较幌，帶...
沈念sama閱讀 35,826評(píng)論 5贊 346
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布，位于F島的核電站白翻，受9級(jí)特大地震影響乍炉，放射性物質(zhì)發(fā)生泄漏绢片。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 41,484評(píng)論 3贊 331
男人毒藥：我在死后第九天來(lái)索命
文/蒙蒙一岛琼、第九天我趴在偏房一處隱蔽的房頂上張望底循。院中可真熱鬧，春花似錦槐瑞、人聲如沸熙涤。這莊子的主人今日做“春日...
開(kāi)封第一講書(shū)人閱讀 32,029評(píng)論 0贊 22
一樁弒父案困檩，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽(yáng)祠挫。三九已至，卻和暖如春悼沿，著一層夾襖步出監(jiān)牢的瞬間等舔，已是汗流浹背。一陣腳步聲響...
開(kāi)封第一講書(shū)人閱讀 33,153評(píng)論 1贊 272
情欲美人皮
我被黑心中介騙來(lái)泰國(guó)打工糟趾，沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留慌植，地道東北人。一個(gè)月前我還...
沈念sama閱讀 48,420評(píng)論 3贊 373
代替公主和親
正文我出身青樓义郑，卻偏偏與公主長(zhǎng)得像蝶柿，于是被迫代替她去往敵國(guó)和親。傳聞我的和親對(duì)象是個(gè)殘疾皇子魔慷，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 45,107評(píng)論 2贊 356

簡(jiǎn)單常用的數(shù)據(jù)泵導(dǎo)出導(dǎo)入(expdp/impdp)命令舉例(下)

1. 首先需要?jiǎng)?chuàng)建Directory

2. 創(chuàng)建表空間和用戶及賦權(quán)

3. 使用impdp導(dǎo)入用戶數(shù)據(jù)

3.1 導(dǎo)入scott用戶的元數(shù)據(jù)，且不包含統(tǒng)計(jì)信息举哟；

3.2 導(dǎo)入scott用戶的數(shù)據(jù)思劳；

3.3 只導(dǎo)入scott用戶下的emp表及數(shù)據(jù)妨猩；

3.4 只導(dǎo)入scott用戶下的emp,dept表結(jié)構(gòu)壶硅；

3.5 導(dǎo)入scott用戶下所有的內(nèi)容；

3.6 并行導(dǎo)入scott用戶下所有的內(nèi)容；

4. 特殊需求

4.1 如果導(dǎo)入環(huán)境的用戶不同拓哺；

4.2 如果導(dǎo)入環(huán)境的表空間也不同；

推薦閱讀更多精彩內(nèi)容