0 积分	1 好友	3 主题

发消息

[物理/逻辑坏块] expdp备份不出索引.

1^#

发表于 2014-3-26 13:16:01 | 查看: 5815| 回复: 4

本帖最后由与晶之恋于 2014-3-31 20:03 编辑

基本信息
oracle版本：10.2.0.4.0
操作系统：HP-UX 11.31

在用expdp导出数据时候发现不完整，发现只能导出表.进一步测试发现有些对象不能导出.比如索引.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SETT"."SYS_EXPORT_SCHEMA_02":  sett/******** SCHEMAS=sett PARALLEL=3 directory=bak_sett_schema dumpfile=bak_sett_schema_test7.dmp LOGFILE=export7.log include=index
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Master table "SETT"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SETT.SYS_EXPORT_SCHEMA_02 is:
  /expdb/sett/sett_schema/bak_sett_schema_test7.dmp
Job "SETT"."SYS_EXPORT_SCHEMA_02" successfully completed at 16:12:40

实际用户是有索引的.

QQ图片20140326131231.jpg

到oracle查看expdp的文档看到schema 模式有如下说明,标红的那句话中的Cross-schema references指的是什么？怎么查询用户的对象是不是cross-schema ？对于这类型的对象expdp怎么导出？
Schema Mode

A schema export is specified using the SCHEMAS parameter. This is the default export mode. If you have the EXP_FULL_DATABASE role, then you can specify a list of schemas and optionally include the schema definitions themselves, as well as system privilege grants to those schemas. If you do not have the EXP_FULL_DATABASE role, you can export only your own schema.

Cross-schema references are not exported unless the referenced schema is also specified in the list of schemas to be exported. For example, a trigger defined on a table within one of the specified schemas, but that resides in a schema not explicitly specified, is not exported. This is also true for external type definitions upon which tables in the specified schemas depend. In such a case, it is expected that the type definitions already exist in the target instance at import time.

分享0

收藏0 回复只看该作者道具举报

与晶之恋

5^#

发表于 2014-3-31 19:59:21

cargoo 发表于 2014-3-27 15:53
检查ALL_OBJECTS，排除当前Schema，看看有什么对象存在

什么意思？排查当前schema？

回复只看该作者道具举报

与晶之恋

4^#

发表于 2014-3-31 19:56:58

本帖最后由与晶之恋于 2014-3-31 20:04 编辑

Liu Maclean(刘相兵发表于 2014-3-27 17:04
expdp 的问题至少给出 expdp语句！

试了很多语句了. 如下都只导出表

expdp sett/*** SCHEMAS=sett PARALLEL=3 directory=bak_sett_schema dumpfile=bak_sett_schema_test5.dmp LOGFILE=export5.log EXCLUDE=\"in \(Select object_name from user_objects where object_name like \'IB_INCOMING_NRTRDE_%\' or object_name like \'IB_INCOMING_CDR_%\' or object_name like \'IB_OUTGOING_CDR_%\' or object_name like \'IB_CDR_%\'\)\"

expdp sett/*** SCHEMAS=sett PARALLEL=3 directory=bak_sett_schema dumpfile=bak_sett_schema_test4.dmp LOGFILE=export4.log INCLUDE=FUNCTION,INDEX,PROCEDURE,PACKAGE,INDEX,TABLE:\"not in \(Select object_name from user_objects where object_name like \'IB_INCOMING_NRTRDE_%\' or object_name like \'IB_INCOMING_CDR_%\' or object_name like \'IB_OUTGOING_CDR_%\' or object_name like \'IB_CDR_%\' \)\"

expdp sett/*** SCHEMAS=sett PARALLEL=3 directory=bak_sett_schema dumpfile=bak_sett_schema_test3.dmp INCLUDE=SEQUENCE,FUNCTION,INDEX,PROCEDURE,PACKAGE,INDEX,TABLE:\"in \(Select object_name from dba_objects where owner = \'SETT\' and object_name NOT like \'IB_INCOMING_NRTRDE_%\' and object_name NOT like \'IB_INCOMING_CDR_%\' and object_name NOT like \'IB_OUTGOING_CDR_%\' and object_name NOT like \'IB_CDR_%\'\)\"

回复只看该作者道具举报