oracle找不到exp,Exp-00003 no storage definition found issue in oracle 11g (未找到段 (0,0) 的存储定义)...

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Tes

已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

服务器使用 AL32UTF8 字符集 (可能的字符集转换)

正在导出簇定义

… (had truncated)

…

. 即将导出 anbob 的表通过常规路径…

. . 正在导出表 ANBOB_IMAG

EXP-00003: 未找到段 (0,0) 的存储定义

. . 正在导出表 ANBOB_ITEM导出了 16 行

. . 正在导出表 ANBOB_UNION导出了 4 行

. . 正在导出表 ANBOB_INDICATOR_LIMIT

EXP-00003: 未找到段 (0,0) 的存储定义

. . 正在导出表 ANBOB_CREDIT导出了 6 行

EXP-00003: no storage definition found for segment (0, 0)

. . 正在导出表 ANBOB_MODEL导出了 1 行

. . 正在导出表 ANBOB_FILE

EXP-00003: 未找到段 (0,0) 的存储定义

. . 正在导出表 ANBOB_FILE_DETAILS

EXP-00003: 未找到段 (0,0) 的存储定义

. . 正在导出表 ANBOB_DEF0000000004

[oracle@db231 ~]$ oerr exp 3

00003, 00000, “no storage definition found for segment(%lu, %lu)”

// *Cause: Export could not find the storage definitions for a cluster,

// index, or table.

// *Action: Record the accompanying messages and report this as an Export

// internal error to customer support.

Diagnosis:

We could not find a storage clause for the named segment.

– The arguments above are file and block, referred to here as

F and B

– First determine what the segment is:

select * from dba_segments

where HEADER_FILE = F

and HEADER_BLOCK = B;

– If it is an INDEX see Bug:231159

– If it is a TABLE it may be best to run this to find table SEGMENTS

whose segment owner differs from the OBJECT owner:

select o.owner#, o.name, o.type#, o.namespace, s.user#

from obj$ o, tab$ t, seg$ s

where o.obj#=t.obj#

and t.file#=s.file#

and t.block#=s.block#

and o.owner#!=s.user#

;

Any rows returned should be investigated.

Solution:

1) run this sql, if something not like 0 returns move those indexes, if 0 returns go to step 2

select count(*) from dba_indexes where owner<>table_owner;

2) Rerun the export and specify the parameter COMPRESS=Y (this is the default value for COMPRESS).

if the error still occurs,

3) Copy $ORACLE_HOME/rdbms/admin/catexp.sql to $ORACLE_HOME/rdbms/admin/catexp2.sql

add this line to $ORACLE_HOME/rdbms/admin/catexp2.sql:

“UNION ALL select * from sys.exu9tneb”

run

$ORACLE_HOME/rdbms/admin/catexp2.sql

if the error still occurs,

4) Upgrade your client version. Your version must be equal or higher than exp version of your target db.

5) Check those tables with exp error had created segment? if not? try to allocate the segments space manully using this sql:

alter table xx allocate extent

6) I recommend try to using expdp instend of exp

This case solution is following:

sys@anbob>@p segment_cre

NAME VALUE

—————————————- —————————————-

deferred_segment_creation FALSE

I guess those tables that to export error should be created before modifying parameters deferred_segment_creation=false. and the exp client version is 11.2.0.1 lower than target DB version 11.2.0.4. when I using above 5# way, to allocate the segment’s extent, try to exp again, it is worked fine at all. the exp-3 error does not throw again.

References MOS note[443453.1]

打赏

微信扫一扫，打赏作者吧～