6 Replies Latest reply: Dec 4, 2012 8:47 PM by CoolBond RSS

    exp 报EXP-00008: ORACLE error 904 encountered;ORA-00904错误

    975356
      环境:RAC11gR2+Redhat6.1

      [oracle@bppfedb1 mouse]$ exp BPPF_EAS/ApNCNOZqGx BUFFER=64000 FILE='/home/oracle/mouse/exp_bppf_tvs20121126.DMP' log='/home/oracle/mouse/exp_bppf_tvs20121126.log' OWNER=BPPF_EAS

      Export: Release 11.2.0.3.0 - Production on Tue Nov 27 00:12:16 2012

      Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.


      Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
      OLAP, Data Mining,
      Export done in US7ASCII character set and AL16UTF16 NCHAR character set
      server uses ZHS16GBK character set (possible charset conversion)

      About to export specified users ...
      . exporting pre-schema procedural objects and actions
      . exporting foreign function library names for user BPPF_EAS
      . exporting PUBLIC type synonyms
      EXP-00008: ORACLE error 904 encountered
      ORA-00904: : invalid identifier
      EXP-00000: Export terminated unsuccessfully

      使用
      expdp BPPF_EAS/ApNCNOZqGx directory=DATA_PUMP_DIR dumpfile=exp_bppf_tvs20121126.dmp schemas=BPPF_EAS logfile=exp_bppf_tvs20121126.log

      换用expdp可以成功

      尝试了几台服务器都遇到这个问题,暂时没有找到问题的原因
        • 1. Re: exp 报EXP-00008: ORACLE error 904 encountered;ORA-00904错误
          LiuMaclean(刘相兵)
          EXP 加上TRACE=Y 并把生成的TRACE发给我

          有问题请去OTN中文论坛开个帖子 我会回复 地址:http://www.otncn.org
          如果需要发送附件,可以直接发邮件到 liu.maclean@gmail.com
          • 2. Re: exp 报EXP-00008: ORACLE error 904 encountered;ORA-00904错误
            CoolBond
            maclean你好,我代楼主发帖,我跟他是同事,我手机了一下trace,通过以下SQL找到的trace目录
            SQL> SELECT value FROM v$diag_info WHERE name='Default Trace File';
            发你邮箱,麻烦你有空指导一下,谢谢
            • 3. Re: exp 报EXP-00008: ORACLE error 904 encountered;ORA-00904错误
              LiuMaclean(刘相兵)
              在你给出的TRACE里找到的内容:

              PARSE ERROR #140397199813648:len=301 dep=0 uid=164 oct=3 lid=164 tim=1354631507795143 err=904
              SELECT SYNNAM, DBMS_JAVA.LONGNAME(SYNNAM), DBMS_JAVA.LONGNAME(SYNTAB), TABOWN, TABNODE, PUBLIC$, SYNOWN, SYNOWNID, T
              ABOWNID, SYNOBJNO FROM SYS.EXU9PTS WHERE SYNOBJNO IN ( SELECT SYNOBJNO FROM SYS.EXU9TYPT
              WHERE TABOBJNO = :1 ) ORDER BY SYNTIME



              PARSE ERROR #解析失败 指向 DBMS_JAVA.LONGNAME JAVA对象
              EXPORT FAILS WITH ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier [ID 436355.1]
              
              Applies to:
              
              Oracle Server - Enterprise Edition - Version 9.2.0.5 to 9.2.0.8 [Release 9.2]
              Information in this document applies to any platform.
              ***Checked for relevance on 30-Jun-2012***
              
              
              Symptoms
              
              Exporting produces the following error:
              Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
              With the Partitioning, OLAP and Oracle Data Mining options
              JServer Release 9.2.0.5.0 - Production
              Export done in ***** character set and ***** character set
              server uses ***** character set (possible charset conversion)
              
              About to export specified tables via Conventional Path ...
              . . exporting table *******
              EXP-00008: ORACLE error 904 encountered
              ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier
              . . exporting table *******
              EXP-00008: ORACLE error 1003 encountered
              ORA-01003: no statement parsed
              . . exporting table *******
              
              .
              
              Cause
              
              The JVM package DBMS_JAVA is missing / invalid and the package is used by the Export Process if the JVM is installed in the database:
              
              Use the following select statement to determine the status of the DBMS_JAVA Package / Package Body and Public Synonym
              
              
              SQL> select owner, status, object_name, object_type
                 from all_objects
                 where object_name like '%DBMS_JAVA%';
              
              
              
              
              .
              
              Solution
              
              Contact Oracle Support Services (OSS) to evaluate the status of the Oracle JVM.  Follow the note detailed below to determine the JVM status and upload the generated log file (jvm_stats.log) to OSS.
              
              Note 456949.1 Script to Check the Status of the JVM within the Database
              
              In most situations the DBMS_JAVA package / package body and synonym can be recreated running the following script as SYS:
              
               
              
              $ORACLE_HOME/javavm/install/initdbj.sql
               
              
              However, it is advised to check with OSS prior to running this script in case other components of the JVM are also invalid or missing.
              • 4. Re: exp 报EXP-00008: ORACLE error 904 encountered;ORA-00904错误
                975356
                问题的原因好像找到了,因为前段时间做安全加固时有个语句:
                revoke execute on sys.dbms_java from public;

                我刚才重新授权
                grant execute on sys.dbms_java to public;

                exp 导出可以了。


                现在有另一个疑问,关于exp trace=y的时候需要的权限问题

                测试床:Suse + 11g
                create user mouse identified by mouse_2012
                default tablespace users
                temporary tablespace temp
                quota unlimited on users ;
                grant connect to mouse;
                grant resource to mouse;
                grant select any table to mouse;
                grant select any dictionary to mouse;
                grant exp_full_database to mouse;

                conn mouse/mouse_2012
                create table test as select * from dba_users;

                测试导出:
                oracle@linux:~/mouse> exp mouse/mouse_2012 tables=test file=test.dmp feedback=100000 buffer=10000000 indexes=y triggers=y trace=y;

                Export: Release 11.2.0.1.0 - Production on Wed Dec 5 06:46:42 2012

                Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.


                EXP-00056: ORACLE error 1031 encountered
                ORA-01031: insufficient privileges
                EXP-00000: Export terminated unsuccessfully


                exp mouse/mouse_2012 tables=test file=test.dmp feedback=100000 buffer=10000000 indexes=y triggers=y
                不带trace=y 是可以导出,

                我grant dba to mouse;
                exp mouse/mouse_2012 tables=test file=test.dmp feedback=100000 buffer=10000000 indexes=y triggers=y trace=y;
                也可以。

                所以我想问下这个trace需要什么权限。。
                • 5. Re: exp 报EXP-00008: ORACLE error 904 encountered;ORA-00904错误
                  975356
                  问题已解决:
                  ORACLE error 904 问题是因为回收了public 的dbms_java执行权限,重新授予解决。

                  trace=y需要的权限问题: 应该需要grant dba to xxx; 单exp_full_database好像不行,下面是测试的情况

                  when the Data Pump TRACE parameter is used in Oracle10g Release 2 or higher, then the user who connects to the database and runs the export DataPump job needs to have the DBA role or the EXP_FULL_DATABASE role (the same applies to Import DataPump with TRACE parameter: DBA or IMP_FULL_DATABASE role required), e.g.:

                  GRANT exp_full_database TO scott;
                  -- or:
                  GRANT dba TO expdp_role;



                  测试:


                  oracle@linux:~/mouse> sqlplus /nolog

                  SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 5 07:23:00 2012

                  Copyright (c) 1982, 2009, Oracle. All rights reserved.

                  SQL> conn /as sysdba
                  Connected.
                  SQL> drop user mouse cascade;

                  User dropped.

                  SQL> create user mouse identified by mouse_2012
                  2 default tablespace users
                  3 temporary tablespace temp
                  4 quota unlimited on users ;
                  grant connect to mouse;
                  grant resource to mouse;
                  grant select any table to mouse;
                  grant select any dictionary to mouse;
                  grant exp_full_database to mouse;
                  User created.

                  SQL>
                  Grant succeeded.

                  SQL>
                  Grant succeeded.

                  SQL>
                  Grant succeeded.

                  SQL>
                  Grant succeeded.

                  SQL>

                  Grant succeeded.

                  SQL>
                  SQL>
                  SQL> conn mouse/mouse_2012
                  create table test as select * from dba_users;Connected.
                  SQL>

                  Table created.

                  SQL> !
                  oracle@linux:~/mouse> exp mouse/mouse_2012 tables=test file=test.dmp feedback=100000 buffer=10000000 indexes=y triggers=y trace=y;
                  Export: Release 11.2.0.1.0 - Production on Wed Dec 5 07:24:35 2012

                  Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.


                  EXP-00056: ORACLE error 1031 encountered
                  ORA-01031: insufficient privileges
                  EXP-00000: Export terminated unsuccessfully
                  oracle@linux:~/mouse> exit
                  exit

                  SQL> conn /as sysdba
                  Connected.
                  SQL> grant dba to mouse;

                  Grant succeeded.

                  SQL> !
                  oracle@linux:~/mouse> exp mouse/mouse_2012 tables=test file=test.dmp feedback=100000 buffer=10000000 indexes=y triggers=y trace=y;

                  Export: Release 11.2.0.1.0 - Production on Wed Dec 5 07:24:50 2012

                  Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.


                  Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
                  With the Partitioning, OLAP, Data Mining and Real Application Testing options
                  Export done in US7ASCII character set and AL16UTF16 NCHAR character set
                  server uses ZHS16GBK character set (possible charset conversion)

                  About to export specified tables via Conventional Path ...
                  . . exporting table TEST
                  33 rows exported
                  Export terminated successfully without warnings


                  继续跟踪
                  • 6. Re: exp 报EXP-00008: ORACLE error 904 encountered;ORA-00904错误
                    CoolBond
                    谢谢maclean,已经解决
                    问题是我们做安全加固时,回收了pulic执行dbms_java的权限
                    grant execute on sys.dbms_java to public;
                    解决了