eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« dbms_sqltune解析SQL的BIND_DATA绑定变量值 | Blog首页 | 诊断案例分享 - ACOUG 11月活动结束 »

故障分析:ORA-00904:invalid column name
modb.pro

今天在用户现场接触一个8i的数据库,好久未接触,差点就忘了SVRMGRL工具了。

客户数据库在导出时一直存在一个错误,问题出现在导出同义词阶段:
. exporting synonyms
EXP-00008: ORACLE error 904 encountered
ORA-00904: invalid column name
EXP-00000: Export terminated unsuccessfully

使用DBMS_SYSTEM跟踪一下导出,发现最后出错停留在以下SQL上:
SELECT SYNNAM, DBMS_JAVA.LONGNAME(SYNNAM), DBMS_JAVA.LONGNAME(SYNTAB), TABOWN, TABNODE, PUBLIC$, SYNOWN, SYNOWNID  FROM SYS.EXU8SYN WHERE SYNOWNID=:1 ORDER BY SYNTIME

手工执行这个脚本出现同样的错误:
SQL> SELECT SYNNAM,
  2         DBMS_JAVA.LONGNAME(SYNNAM),
  3         DBMS_JAVA.LONGNAME(SYNTAB),
  4         TABOWN,
  5         TABNODE,
  6         PUBLIC$,
  7         SYNOWN,
  8         SYNOWNID
  9    FROM SYS.EXU8SYN
 10  /
 
SELECT SYNNAM,
       DBMS_JAVA.LONGNAME(SYNNAM),
       DBMS_JAVA.LONGNAME(SYNTAB),
       TABOWN,
       TABNODE,
       PUBLIC$,
       SYNOWN,
       SYNOWNID
  FROM SYS.EXU8SYN
 
ORA-00904: invalid column name

发现又是java对象的问题:
SQL> select owner, status, object_type, object_name from all_objects
  2  where object_name like'%DBMS_JAVA%';
 
OWNER                          STATUS  OBJECT_TYPE        OBJECT_NAME
------------------------------ ------- ------------------ ------------------------------
SYS                            VALID   PACKAGE            DBMS_JAVA_TEST
SYS                            VALID   PACKAGE BODY       DBMS_JAVA_TEST
PUBLIC                         VALID   SYNONYM            DBMS_JAVA_TEST

Metalink上可以找到大量相关的文档记录,解决方案是,要么完全无误的安装JAVA对象,要么彻底移除,移除可以运行rmjvm.sql脚本,该脚本位于$ORACLE_HOME/javavm/install/rmjvm.sql
If Java is enabled, ensure that both DBMS_JAVA synonyms and packages are created and valid.
The following script can be used to create the required packages,
"$ORACLE_HOME/javavm/install/initdbj.sql". This will need to be run as SYSDBA.

If Java is disabled, run "$ORACLE_HOME/javavm/install/rmjvm.sql" to remove Java related
objects. Do not just drop the synonym.
记录一下备查。



历史上的今天...
    >> 2019-11-25文章:
    >> 2014-11-25文章:
    >> 2011-11-25文章:
    >> 2005-11-25文章:

By eygle on 2010-11-25 17:25 | Comments (0) | Case | 2665 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com