exp匯出出現：ORA-00904: : invalid identifier

尛樣兒發表於2014-11-17
IDE

相關文章：
   《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: : 識別符號無效  <<<< 兩個冒號之間是空格，並沒有具體的識別符號，這跟以前遇到過的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--

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23135684/viewspace-1336722/，如需轉載，請註明出處，否則將追究法律責任。

相關文章