본문 : http://www.ischo.net -- 조인상 //시스템 엔지니어

+++++++++++++++++++++++++++++++++++++++++++++++++++++++


상황 : 오라클 8.0 및 오라클 8i 에서 exp 한 dump파일을

오라클 9i 9.2.0.4 (release 2 설치 후 패치셋 3 적용) 데이터베이스에

imp 시킨 후....


데이터베이스 정기백업을 위해서 exp를 실행한 후 다음과 같은 메시지를

보이며 exp가 정지한다.


( 문제 1 )


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses KO16KSC5601 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully


( 문제 1 해결책 )

위 문제는 오라클 하위버전 덤프를 상위버전에 imp 시켰을 경우에

발생하며 $ORACLE_HOME/rdbms/admin/catexp.sql 을

구동시키면 해결된다.


root# su - oracle
oracle$ sqlplus '/ as sysdba'
sql> start rdbms/admin/catexp.sql





( 문제 2 )

위와 같은 상황에서

exp 실행시 다음과 같은 에러를 보이며 exp가 정지한다.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses KO16KSC5601 character set (possible charset conversion)

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
EXP-00056: ORACLE error 31600 encountered
ORA-31600: invalid input value EMIT_SCHEMA for parameter NAME in function SET_TRANSFORM_PARAM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3926
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4050
ORA-06512: at "SYS.DBMS_METADATA", line 836
ORA-06512: at line 1
EXP-00056: ORACLE error 31600 encountered
ORA-31600: invalid input value EMIT_SCHEMA for parameter NAME in function SET_TRANSFORM_PARAM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3926
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4050
ORA-06512: at "SYS.DBMS_METADATA", line 836
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully



위 문제는 패치셋3 적용후에 발생하며

$ORACLE_HOME/rdbms/admin/catpatch.sql을 실행시키면 해결된다.


root# su - oracle
oracle$ sqlplus '/ as sysdba'
sql> start rdbms/admin/catpatch.sql