impdp 将数据导入数据库中不存的用户里

没有开场白,直接看测试吧

SQL>  create or replace directory dir1 as '/backup/test/';

Directory created.

SQL> grant read,write on directory dir1 to yxyup;

Grant succeeded.

SQL> conn yxyup
Enter password:
Connected.
SQL> create table t1 as select * from dba_objects;

Table created.

oracle@hpdata3:/backup/test#expdp system/system dumpfile=yxyup.dump directory=dir1 schemas=yxyup logfile=yxyup.log

Export: Release 10.2.0.3.0 - 64bit Production on Thursday, 26 June, 2008 16:53:55

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, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** dumpfile=yxyup.dump directory=dir1 schemas=yxyup logfile=yxyup.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 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
. . exported "YXYUP"."T1"                                4.718 MB   50531 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /backup/test/yxyup.dump
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:54:16

SQL> drop table t1 purge;
 Dropped.

oracle@hpdata3:/backup/test#impdp system/system dumpfile=yxyup.dump directory=dir1schemas=yxyuplogfile=yxyup.log.imp

Import: Release 10.2.0.3.0 - 64bit Production on Thursday, 26 June, 2008 16:56:49

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, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** dumpfile=yxyup.dump directory=dir1 schemas=yxyup logfile=yxyup.log.imp
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"YXYUP" 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 "YXYUP"."T1"                                4.718 MB   50531 rows
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 16:56:59

oracle@hpdata3:/backup/test#impdp system/system dumpfile=yxyup.dump directory=dir1schemas=aaalogfile=yxyup.log.imp

Import: Release 10.2.0.3.0 - 64bit Production on Thursday, 26 June, 2008 16:58:05

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, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39165: Schema AAA was not found.

数据库已经存在的用户,在impdp时说ORA-31684: Object type USER:"YXYUP" already exists(当然可以导入成功)

数据库中不存的用户,在impdp 说ORA-39165: Schema AAA was not found.(这里自然成功不了)

如果想导入到其他用户中可以用remap_schema来做,(像import时的fromuser,touser)

oracle@hpdata3:/backup/test#impdp system/system dumpfile=yxyup.dump directory=dir1 remap_schema=yxyup:aaa logfile=yxyup.log.imp

Import: Release 10.2.0.3.0 - 64bit Production on Thursday, 26 June, 2008 17:17:01

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, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** dumpfile=yxyup.dump directory=dir1 remap_schema=yxyup:aaa logfile=yxyup.log.imp
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 "AAA"."T1"                                  4.718 MB   50531 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 17:17:10

oracle@hpdata3:/backup/test#impdp system/system dumpfile=yxyup.dump directory=dir1 remap_schema=yxyup:bbb logfile=yxyup.log.imp

Import: Release 10.2.0.3.0 - 64bit Production on Thursday, 26 June, 2008 17:22:52

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, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** dumpfile=yxyup.dump directory=dir1 remap_schema=yxyup:bbb logfile=yxyup.log.imp
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 "BBB"."T1"                                  4.718 MB   50531 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 17:23:01