本文介绍了解决使用较低版本Export工具从Oracle 9.2.0.5及更高版本数据库导出数据时出现的EXP-00003错误的方法。此问题发生在当表或大型对象分配了多个段扩展时。文章提供了两种解决方案，包括修改导出定义。
使用9.2.0.1的export导出工具 导出 10.2.0.1的数据库中的数据，确实有问题。exp-00003
Oracle Server - Personal Edition - Version: 8.1.7.0 to 9.2.0.4
Oracle Server - Standard Edition - Version: 8.1.7.0 to 9.2.0.4
This problem can occur on any platform.
Table being exported is in Oracle9i 9.2.0.5.0 or any higher source database.
Table itself or Large Object (LOB) in table has more than one extent allocated.
The version of the Export utility is Pre-9.2.0.5.0.
Export can be either table level or schema level or database level.
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table DUMMY 1 rows exported
. . exporting table EMP 14 rows exported
. . exporting table EMP2EXTENT
EXP-00003: no storage definition found for segment(9, 907)
. . exporting table SALGRADE 5 rows exported
. exporting synonyms
... (left out remaining output)
Export terminated successfully with warnings.
As a result of that fix, an export from the 9.2.0.5.0 or any higher release database (e.g. 9.2.0.6.0 or 10.1.0.4.0) with a lower release export utility, e.g. 9.2.0.4.0 or 8.1.7.4.0 will now produce the EXP-3 error. For details see:
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)"
These bugs were closed as a duplicate of:
Bug 3291851 "EXP-3: NO STORAGE DEFINITION FOUND FOR SEGMENT" (not a public bug)
or any higher release database (e.g. 9.2.0.6.0 or 10.1.0.4.0) with a 9.2.0.4.0 export utility, is to import the export dump file later into a target database of that lower release.
Note: if your intention is to export from a 9.2.0.6.0 database in order to import (back) into a 9.2.0.6.0 target database, there is no need to use a pre-9.2.0.5 release export utility. In that situation use the 9.2.0.6.0 export utility.
Workaround 1
-------------
Rerun the export and specify the parameter COMPRESS=Y (this is the default value for COMPRESS).
If you have a table with a secondary object, e.g. a LOB column, then this workaround might not work and you will re-produce the EXP-3 error during export. In that case, use workaround 2.
Workaround 2
-------------
a. Make a note of the export definition of exu9tne from $ORACLE_HOME/rdbms/admin/catexp.sql
b. Copy this to a new file and add:
"UNION ALL select * from sys.exu9tneb"
to the end of the definition
e.g. In the 9.2.0.5.0 or any higher release source database (e.g. 9.2.0.6.0 or 10.1.0.4.0), the workaround view would be:
CREATE OR REPLACE VIEW exu9tne (
tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1
UNION ALL
SELECT * from sys.exu9tneb
/
c. Run this as the SYS user (!) against the database that needs to be exported.
d. Re-run the export as required.
e. Put back the original definition of exu9tne as obtained in step a.
Note: only use this workaround when absolutely necessary, and don't forget to put back the original definition of the view SYS.exu9tne as mentioned in step e.
Bug 3593227 - Exp-3 When Exporting A Table With More Than 1 Extent Using Exp < 9.2.0
Bug 3784697 - Export Of Lob From 9205 With Pre-9205 Exp Gives Exp-3 (No Storage Def Found)
ORACLE文档提供了相关资料：
使用9.2.0.5以下的export导出工具，导出9.2.0.5或以上版本的数据库的数据，的确会导致报错exp-00003.
具体的bug内容，ORACLE并没有公开。
解决方法：
确实就是您在文档中所查到的方法。
我发给您的文档中，“Workaround 2”就是描述了这样的方法。
[@more@]
| Doc ID: | 274076.1 | Type: | PROBLEM | |
| Modified Date : | 04-JUL-2008 | Status: | PUBLISHED |
The information in this document applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.0 to 9.2.0.4Oracle Server - Personal Edition - Version: 8.1.7.0 to 9.2.0.4
Oracle Server - Standard Edition - Version: 8.1.7.0 to 9.2.0.4
This problem can occur on any platform.
Table being exported is in Oracle9i 9.2.0.5.0 or any higher source database.
Table itself or Large Object (LOB) in table has more than one extent allocated.
The version of the Export utility is Pre-9.2.0.5.0.
Export can be either table level or schema level or database level.
Symptoms
... (left out previous output). about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table DUMMY 1 rows exported
. . exporting table EMP 14 rows exported
. . exporting table EMP2EXTENT
EXP-00003: no storage definition found for segment(9, 907)
. . exporting table SALGRADE 5 rows exported
. exporting synonyms
... (left out remaining output)
Export terminated successfully with warnings.
Cause
This problem during export has been introduced with the 9.2.0.5 patchset. In that patchset, a fix has been included for Bug 2948717 "Queries against DBA_SEGMENTS can be slow accessing BYTES/BLOCKS/EXTENTS columns". Note that this fix has also been implemented in Oracle10g Release 1 (10.1.0.2.0).As a result of that fix, an export from the 9.2.0.5.0 or any higher release database (e.g. 9.2.0.6.0 or 10.1.0.4.0) with a lower release export utility, e.g. 9.2.0.4.0 or 8.1.7.4.0 will now produce the EXP-3 error. For details see:
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)"
These bugs were closed as a duplicate of:
Bug 3291851 "EXP-3: NO STORAGE DEFINITION FOUND FOR SEGMENT" (not a public bug)
Solution
The EXP-3 error only occurs when the export from the 9.2.0.5.0 or any higher release database (e.g. 9.2.0.6.0 or 10.1.0.4.0) is done with a lower release export utility, e.g. 9.2.0.4.0. The reason to run an export from a 9.2.0.5.0or any higher release database (e.g. 9.2.0.6.0 or 10.1.0.4.0) with a 9.2.0.4.0 export utility, is to import the export dump file later into a target database of that lower release.
Note: if your intention is to export from a 9.2.0.6.0 database in order to import (back) into a 9.2.0.6.0 target database, there is no need to use a pre-9.2.0.5 release export utility. In that situation use the 9.2.0.6.0 export utility.
Workaround 1
-------------
Rerun the export and specify the parameter COMPRESS=Y (this is the default value for COMPRESS).
If you have a table with a secondary object, e.g. a LOB column, then this workaround might not work and you will re-produce the EXP-3 error during export. In that case, use workaround 2.
Workaround 2
-------------
a. Make a note of the export definition of exu9tne from $ORACLE_HOME/rdbms/admin/catexp.sql
b. Copy this to a new file and add:
"UNION ALL select * from sys.exu9tneb"
to the end of the definition
e.g. In the 9.2.0.5.0 or any higher release source database (e.g. 9.2.0.6.0 or 10.1.0.4.0), the workaround view would be:
CREATE OR REPLACE VIEW exu9tne (
tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1
UNION ALL
SELECT * from sys.exu9tneb
/
c. Run this as the SYS user (!) against the database that needs to be exported.
d. Re-run the export as required.
e. Put back the original definition of exu9tne as obtained in step a.
Note: only use this workaround when absolutely necessary, and don't forget to put back the original definition of the view SYS.exu9tne as mentioned in step e.
References
Bug 2948717 - Performance Of Query On Dba_Segments Is Bad When Accessing Any "Size" ColumnBug 3593227 - Exp-3 When Exporting A Table With More Than 1 Extent Using Exp < 9.2.0
Bug 3784697 - Export Of Lob From 9205 With Pre-9205 Exp Gives Exp-3 (No Storage Def Found)
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/87149/viewspace-1028313/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/87149/viewspace-1028313/
扫一扫
1707
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
