오라클 9i 환경에서 테이블 내 특정 컬럼이 LOB 또는 XML 타입인 경우 발생하는 에러현상

오라클 9i 환경에서 테이블 단위로 export 과정에서 다음과 같은 에러가 발생했습니다.
다른 테이블은 export 시 전혀 문제 없었는데 특정 테이블만 계속 에러가 발생하니 급 당황스럽네요.

... 생략 ...
EXP-00003: 세그먼트(10, 209)에 대한 저장 영역 정의가 없습니다


관련하여 구글 및 메타링크를 확인해보니,,
오라클 9i 환경에서 테이블 내 특정 컬럼이 LOB 또는 XML 타입인 경우 발생하는 에러라고 하네요.
좀 그런건,, 유저 단위 또는 풀 백업시는 문제가 발생한 테이블도 export 가 너무나 잘 된다는거.. **

Oracle 9i 환경에서 LOB 또는 XML 컬럼이 포함된 테이블을 테이블 단위로 export 할 때 발생하는 것 같습니다.

메타링크에 임시 해결 방안에 대해 안내가 되어 있어,,
sys 계정에서 exu9tne 뷰를 조금 수정한 후 export 했더니 드디어 성공했습니다.

출처:  https://metalink.oracle.com/



EXP-00003 When Exporting From 9.2.0.5.0 or any Higher Release with a Pre-9.2.0.5.0 Export Client


--------------------------------------------------------------------
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.0 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.



출처 : http://kcdl.tistory.com/176