exp导出出现:ORA-00904: : invalid identifier

简介: 相关文章:    《exp导出出现:ORA-00904: "POLTYP": invalid identifier》:http://blog.itpub.net/23135684/viewspace-680615/     客户反映将JFY用户下的某个表的字...

相关文章:
   《exp导出出现:ORA-00904: "POLTYP": invalid identifier》:http://blog.itpub.net/23135684/viewspace-680615/

    客户反映将JFY用户下的某个表的字段由VARCHAR2(50)调整为VARCHAR2(100)之后,执行exp导出时收到如下的报错:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: grants on tables/views/sequences/roles will not be exported

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

   客户的exp导出操作是在数据库服务器段完成的,且调度脚本已经稳定运行了1年多,在调整字段长度后出现该报错。脚本中exp的语句如下:
exp jfy/xxx BUFFER=8192 FILE=$bakdir$myfile.dmp ROWS=y COMPRESS=y CONSTRAINTS=y GRANTS=n INDEXES=y LOG=$bakdir$mylogfile
jfy是个DBA权限的用户。

根据以上的错误情况,在MOS中找到如下符合描述的文章:

Errors EXP-8 ORA-904 During Export (Doc ID 1091927.1)

In this Document

Symptoms
Cause
Solution


Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.
***Checked for relevance on 27-Jun-2013***

Symptoms

You are attempting to perform an export as the SYSTEM user or a DBA. During export, the following errors occurred:

EXP-00008: ORACLE error 904 encountered
ORA-00904: : invalid identifier  


When performing a trace on the export, we see that the ORA-904 error is caused by the following statement:

PARSE ERROR #9:len=302 dep=0 uid=5 oct=3 lid=5 tim=778463694169 err=904
SELECT SYNNAM, DBMS_JAVA.LONGNAME(SYNNAM), DBMS_JAVA.LONGNAME(SYNTAB), TABOWN, TABNODE, PUBLIC$, SYNOWN, SYNOWNID, TABOWNID, SYNOBJNO FROM SYS.EXU9PTS WHERE SYNOBJNO IN ( SELECT SYNOBJNO FROM SYS.EXU9TYPT WHERE TABOBJNO = :1 ) ORDER BY SYNTIME

Cause

The PUBLIC privilege has been revoked from the DBMS_JAVA package.

Privileges to the DBMS_JAVA package are needed for the export to succeed. However, as there is concern about security issues due to privileges on the DBMS_JAVA many have revoked the execute privilege to this package to keep the database secure.

Solution

Explicitly grant EXECUTE on the DBMS_JAVA package to the DBA role:

SQL> grant execute on DBMS_JAVA to dba;


This will allow members of the DBA role to have the rights to perform the export while keeping the DBMS_JAVA package out of the PUBLIC role.


     按照文章的提示,执行grant execute on DBMS_JAVA to dba;语句之后exp操作恢复正常。
   出现这个问题的原因是PUBLIC的DBMS_JAVA包权限被回收,要想 执行exp导出成功需要DBMS_JAVA的execute权限,然而担心到安全的问题,回收了DBMS_JAVA包的执行权限,以保证数据库的安全。

    按照Oracle的解释,那么一定是某种不安全的操作触发了Oracle因为安全的原因 回收了PUBLIC的DBMS_JAVA execute权限,从而导致的该报错。

--end--

目录
相关文章
|
Oracle 关系型数据库 数据库
ORA-00932: inconsistent datatypes: expected - got NCLOB【ORA-00932: 数据类型不一致: 应为 -, 但却获得 NCLOB 】【解决办法】
ORA-00932: inconsistent datatypes: expected - got NCLOB【ORA-00932: 数据类型不一致: 应为 -, 但却获得 NCLOB 】【解决办法】
|
Oracle 关系型数据库 数据库
oracle exp&imp之IMP-00037: Character set marker unknown
    最近,为地市技术人员做技术支持,碰到一个很奇怪的问题,别人给他提供一个exp的dmp文件,他在本地进行imp的时候报错:IMP-00037:Character set marker unknown 报错信息如下: C:\Users\localadmin>i...
4319 0
|
4月前
|
SQL Oracle 关系型数据库
【已解决】ORA-01722: invalid number
【已解决】ORA-01722: invalid number
38 0
|
11月前
|
数据库
ORA-06553: PLS-801: internal error 的解决办法
搜索了一下,原来是把32位的数据库恢复到64位的数据库了。找到解决方案如下
|
SQL Oracle 关系型数据库
ORA-01722:invalid number
Oracle表字段为`VARCHAR2`时,where条件出现`NUMBER`的匹配,`可能`会出现该错误。
218 0
ORA-01722:invalid number
|
Oracle 关系型数据库 数据安全/隐私保护