在《【IMPDP】【IMP】SQL脚本尽收眼底——SHOW参数与SQLFILE参数对比》http://space.itpub.net/519536/viewspace-631290文章中谈到，使用EXPDP导出的dump文件中包含用户的创建信息，因此在使用IMPDP导入数据时，如果用户不存在的情况下，IMPDP工具可以自动完成用户的创建工作（当然，完成导入的用户需要具有创建用户的权限，如使用system用户）。

1.使用EXPDP生成备份文件
sec@secDB /expdp$ expdp sec/sec directory=expdp_dir dumpfile=`date +"%Y%m%d%H%M%S"`_sec.dmp logfile=`date +"%Y%m%d%H%M%S"`_sec.log

Export: Release 10.2.0.3.0 - 64bit Production on Thursday, 01 April, 2010 10:29:17

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "SEC"."SYS_EXPORT_SCHEMA_01": sec/******** directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3 MB
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/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/COMMENT
. . exported "SEC"."T"                                   2.259 MB   24360 rows
Master table "SEC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is:
/expdp/20100401102917_sec.dmp
Job "SEC"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:29:20

2.删除用户sec
sec@ora10g> conn / as sysdba
Connected.
sys@ora10g> drop user sec cascade;

User dropped.

3.使用IMPDP完成数据的导入
sec@secDB /expdp$ impdp system/password directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log

Import: Release 10.2.0.3.0 - 64bit Production on Thursday, 01 April, 2010 10:44:07

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log
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 "SEC"."T"                                   2.259 MB   24360 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 10:44:10

可见，在sec用户自动创建，并完成数据的导入。

4.最终的验证
登录到数据库sec用户，验证数据是否导入成功。
sec@secDB /expdp$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Apr 1 10:44:23 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

sys@ora10g> conn sec/sec
Connected.
sec@ora10g> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
T                              TABLE

sec@ora10g> select count(*) from t;

COUNT(*)
----------
     24360

OK，数据导入完成。

5.小结
IMPDP的用户自动创建功能也体现了Oracle自动化的思想。不过在使用过程中，一定要注意dump文件中创建用户脚本的细节参数，防止带来不必要的麻烦。
虽然IMPDP工具具有自动创建用户的功能，不过尽量不要采用这种方法，还是应该按部就班的手工完成用户的创建及用户授权，然后再完成数据的导入。

Good luck.

secooler
10.04.04

-- The End --

ITPub博客

【IMPDP】使用IMPDP自动创建用户并完成数据的导入

最新文章