EXP-3错误

最新推荐文章于 2024-05-23 18:28:57 发布

ckawt40802

最新推荐文章于 2024-05-23 18:28:57 发布

阅读量194

点赞数

文章标签：数据库

今天在尝试用9204的EXP导出10203数据的时候，出现了EXP-3错误：EXP-00003: no storage definition found for segment(41, 6024)

查询了一下METALINK，发现9205版本之前的EXP客户端，导出9205以上版本的数据时，如果表中包含LOB字段，或者表中的EXTENT超过一个，则会导致EXP-00003错误。

造成问题的原因是由于Oracle在9205及以上版本中，解决了一个查询DBA_SEGMENTS的BYTES/EXTENTS/BLOCKS列速度缓慢的错误。而解决这个bug的代价就是9204及以下版本在导出高版本数据时会碰到EXP-3这个错误。

具体的相关信息可以查询METALINK：

Bug 2948717 : Queries against DBA_SEGMENTS can be slow accessing BYTES/BLOCKS/EXTENTS columns；

Bug 3593227 "EXP-3 WHEN EXPORTING A TABLE WITH MORE THAN 1 EXTENT USING EXP BELOW 9.2.0.5"

Bug 3784697 "EXPORT OF LOB FROM 9205 WITH PRE-9205 EXP GIVES EXP-3 (NO STORAGE DEF FOUND)"

解决问题的方法到是不算复杂：

如果导出不包含LOB字段，那么可以将EXP语句中的COMPRESS参数设置为Y，重新执行导出即可。

如果包含LOB字段，或者上面的方法无效，那么需要对导出库修改exu9tne视图。在导出完成后，再将该视图恢复。

[oracle@demo2 oracle]$ exp yangtk/yangtk@yangtk file=test10203.dmp buffer=20480000 compress=n

Export: Release 9.2.0.4.0 - Production on 星期四 7月 5 11:17:17 2007

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user YANGTK
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user YANGTK
About to export YANGTK's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export YANGTK's tables via Conventional Path ...
. . exporting table A 3 rows exported
. . exporting table AA 4 rows exported
. . exporting table BONUS 0 rows exported
. . exporting table CHAINED_ROWS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table DUMMY 1 rows exported
. . exporting table EMP 14 rows exported
. . exporting table MLOG$_T 7 rows exported
. . exporting table MLOG$_T_ROWID 2 rows exported
. . exporting table MV_CAPABILITIES_TABLE 0 rows exported
. . exporting table MV_T1 0 rows exported
. . exporting table MV_T2 0 rows exported
. . exporting table MV_T3 0 rows exported
. . exporting table MV_T_ID 2 rows exported
. . exporting table MV_T_ID_NAME 0 rows exported
. . exporting table MV_T_NAME 0 rows exported
. . exporting table MV_T_ROWID 0 rows exported
. . exporting table SALGRADE 5 rows exported
. . exporting table T 3 rows exported
. . exporting table T1
EXP-00003: no storage definition found for segment(4, 7)
. . exporting table T2 0 rows exported
. . exporting table T3
EXP-00003: no storage definition found for segment(4, 39)
. . exporting table TEST 3 rows exported
. . exporting table TT 0 rows exported
. . exporting table TT2 0 rows exported
. . exporting table TTT 0 rows exported
. . exporting table T_PARTITION
. . exporting partition P1 3 rows exported
. . exporting partition P2 0 rows exported
. . exporting table T_ROWID 2 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
[oracle@demo2 oracle]$ exp yangtk/yangtk@yangtk file=test10203.dmp buffer=20480000 compress=y

Export: Release 9.2.0.4.0 - Production on 星期四 7月 5 11:17:53 2007

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user YANGTK
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user YANGTK
About to export YANGTK's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export YANGTK's tables via Conventional Path ...
. . exporting table A 3 rows exported
. . exporting table AA 4 rows exported
. . exporting table BONUS 0 rows exported
. . exporting table CHAINED_ROWS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table DUMMY 1 rows exported
. . exporting table EMP 14 rows exported
. . exporting table MLOG$_T 7 rows exported
. . exporting table MLOG$_T_ROWID 2 rows exported
. . exporting table MV_CAPABILITIES_TABLE 0 rows exported
. . exporting table MV_T1 0 rows exported
. . exporting table MV_T2 0 rows exported
. . exporting table MV_T3 0 rows exported
. . exporting table MV_T_ID 2 rows exported
. . exporting table MV_T_ID_NAME 0 rows exported
. . exporting table MV_T_NAME 0 rows exported
. . exporting table MV_T_ROWID 0 rows exported
. . exporting table SALGRADE 5 rows exported
. . exporting table T 3 rows exported
. . exporting table T1 22988 rows exported
. . exporting table T2 0 rows exported
. . exporting table T3 50272 rows exported
. . exporting table TEST 3 rows exported
. . exporting table TT 0 rows exported
. . exporting table TT2 0 rows exported
. . exporting table TTT 0 rows exported
. . exporting table T_PARTITION
. . exporting partition P1 3 rows exported
. . exporting partition P2 0 rows exported
. . exporting table T_ROWID 2 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

如果包含LOB，或者必须设置COMPRESS=Y，可以先用SYS修改exu9tne视图，再导出结束后，再将其恢复。

[oracle@demo2 oracle]$ sqlplus "yangtk/yangtk@yangtk as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on 星期四 7月 5 11:21:51 2007

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

Session altered.

SQL> SET LONG 10000
SQL> select dbms_metadata.get_ddl('VIEW', 'EXU9TNE') FROM DUAL;

DBMS_METADATA.GET_DDL('VIEW','EXU9TNE')
------------------------------------------------------------------------------

CREATE OR REPLACE FORCE VIEW "SYS"."EXU9TNE" ("TSNO", "FILENO", "BLOCKNO", "LENGTH") AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1

SQL> CREATE OR REPLACE VIEW EXU9TNE (TSNO, FILENO, BLOCKNO, LENGTH) AS
2 SELECT TS#, SEGFILE#, SEGBLOCK#, LENGTH
3 FROM SYS.UET$
4 WHERE EXT# = 1
5 UNION ALL
6 SELECT * FROM EXU9TNEB;

View created.

SQL> HOST
[oracle@demo2 oracle]$ exp yangtk/yangtk@yangtk file=test10203.dmp buffer=20480000 compress=n

Export: Release 9.2.0.4.0 - Production on 星期四 7月 5 11:25:27 2007

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user YANGTK
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user YANGTK
About to export YANGTK's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export YANGTK's tables via Conventional Path ...
. . exporting table A 3 rows exported
. . exporting table AA 4 rows exported
. . exporting table BONUS 0 rows exported
. . exporting table CHAINED_ROWS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table DUMMY 1 rows exported
. . exporting table EMP 14 rows exported
. . exporting table MLOG$_T 7 rows exported
. . exporting table MLOG$_T_ROWID 2 rows exported
. . exporting table MV_CAPABILITIES_TABLE 0 rows exported
. . exporting table MV_T1 0 rows exported
. . exporting table MV_T2 0 rows exported
. . exporting table MV_T3 0 rows exported
. . exporting table MV_T_ID 2 rows exported
. . exporting table MV_T_ID_NAME 0 rows exported
. . exporting table MV_T_NAME 0 rows exported
. . exporting table MV_T_ROWID 0 rows exported
. . exporting table SALGRADE 5 rows exported
. . exporting table T 3 rows exported
. . exporting table T1 22988 rows exported
. . exporting table T2 0 rows exported
. . exporting table T3 50272 rows exported
. . exporting table TEST 3 rows exported
. . exporting table TT 0 rows exported
. . exporting table TT2 0 rows exported
. . exporting table TTT 0 rows exported
. . exporting table T_PARTITION
. . exporting partition P1 3 rows exported
. . exporting partition P2 0 rows exported
. . exporting table T_ROWID 2 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
[oracle@demo2 oracle]$ exit
exit

SQL> CREATE OR REPLACE VIEW EXU9TNE (TSNO, FILENO, BLOCKNO, LENGTH) AS
2 SELECT TS#, SEGFILE#, SEGBLOCK#, LENGTH
3 FROM SYS.UET$
4 WHERE EXT# = 1
5 ;

View created.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-69339/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-69339/

ckawt40802

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
EXP-3错误

今天在尝试用9204的EXP导出10203数据的时候，出现了EXP-3错误：EXP-00003: no storage definition found for segment(41, 6024)查询了一下METALINK，发现...
复制链接

扫一扫