在11gr2中,用exp导出数据时,发现新表里如果有数据才能导出来,没有数据就导不出来,所以有下面的方法实现
expdp system/******** dumpfile=dd.dmp schemas=USR reuse_dumpfiles=true nologfile=true
impdp system/******** dumpfile=dd.dmp remap_schema=USR:USR nologfile=true
C:\Documents and Settings\dell>expdp system/oracle file=dd.dmp owner=USR
Export: Release 11.2.0.1.0 - Production on 星期二 8月 24 13:28:26 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
由于以下参数, 旧有模式处于活动状态:
旧有模式参数: "file=dd.dmp" 位置: Command Line, 替换为: "dumpfile=dd.dmp"
旧有模式参数: "owner=USR" 位置: Command Line, 替换为: "schemas=USR"
旧有模式设置了 reuse_dumpfiles=true 参数。
旧有模式设置了 nologfile=true 参数。
启动 "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=dd.dmp schemas=U
SR reuse_dumpfiles=true nologfile=true
正在使用 BLOCKS 方法进行估计...
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 64 KB
处理对象类型 SCHEMA_EXPORT/USER
处理对象类型 SCHEMA_EXPORT/SYSTEM_GRANT
处理对象类型 SCHEMA_EXPORT/ROLE_GRANT
处理对象类型 SCHEMA_EXPORT/DEFAULT_ROLE
处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
处理对象类型 SCHEMA_EXPORT/SEQUENCE/SEQUENCE
处理对象类型 SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE
处理对象类型 SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX
处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
处理对象类型 SCHEMA_EXPORT/TABLE/COMMENT
处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . 导出了 "USR"."USERINFO" 11 KB 3 行
. . 导出了 "USR"."SEARCH_KEYWORDS" 0 KB 0 行
. . 导出了 "USR"."SEARCH_LOG" 0 KB 0 行
. . 导出了 "USR"."USER_CART" 0 KB 0 行
. . 导出了 "USR"."USER_CART_HIS" 0 KB 0 行
. . 导出了 "USR"."USER_COMMENT" 0 KB 0 行
. . 导出了 "USR"."USER_DETAIL" 0 KB 0 行
. . 导出了 "USR"."USER_FAVORITE" 0 KB 0 行
. . 导出了 "USR"."USER_GRADE" 0 KB 0 行
. . 导出了 "USR"."USER_MESSAGE" 0 KB 0 行
. . 导出了 "USR"."USER_RECEIVING_ADDRESS" 0 KB 0 行
. . 导出了 "USR"."USER_SCORE_DETAIL" 0 KB 0 行
. . 导出了 "USR"."USER_SCORE_RULE" 0 KB 0 行
. . 导出了 "USR"."USER_VIRTUAL_ACCOUNT" 0 KB 0 行
已成功加载/卸载了主表 "SYSTEM"."SYS_EXPORT_SCHEMA_01"
******************************************************************************
SYSTEM.SYS_EXPORT_SCHEMA_01 的转储文件集为:
D:\APP\DELL\ADMIN\ORCL\DPDUMP\DD.DMP
作业 "SYSTEM"."SYS_EXPORT_SCHEMA_01" 已于 13:29:22 成功完成
system/oracle dumpfile=dd.dmp remap_schema=USR:USR nologfile=true
Import: Release 11.2.0.1.0 - Production on Tue Aug 24 13:37:10 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.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/******** dumpfile=dd.dmp remap_schema=USR:USR nologfile=true
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/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "USR"."USERINFO" 11 KB 3 rows
. . imported "USR"."SEARCH_KEYWORDS" 0 KB 0 rows
. . imported "USR"."SEARCH_LOG" 0 KB 0 rows
. . imported "USR"."USER_CART" 0 KB 0 rows
. . imported "USR"."USER_CART_HIS" 0 KB 0 rows
. . imported "USR"."USER_COMMENT" 0 KB 0 rows
. . imported "USR"."USER_DETAIL" 0 KB 0 rows
. . imported "USR"."USER_FAVORITE" 0 KB 0 rows
. . imported "USR"."USER_GRADE" 0 KB 0 rows
. . imported "USR"."USER_MESSAGE" 0 KB 0 rows
. . imported "USR"."USER_RECEIVING_ADDRESS" 0 KB 0 rows
. . imported "USR"."USER_SCORE_DETAIL" 0 KB 0 rows
. . imported "USR"."USER_SCORE_RULE" 0 KB 0 rows
. . imported "USR"."USER_VIRTUAL_ACCOUNT" 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/COMMENT
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 13:37:17
impdp自己创建用户,不用提前创建
阅读(1079) | 评论(1) | 转发(0) |