exp导出出现：ORA

peiyuan1030

　　相关文章：

　　《exp导出出现：ORA-00904: "POLTYP": invalid>　　
　　客户反映将JFY用户下的某个表的字段由VARCHAR2(50)调整为VARCHAR2(100)之后，执行exp导出时收到如下的报错：
　　Connected to: Oracle Database 11g Enterprise Edition>
　　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>
　　
　　
　　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>
　　
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>
　　
　　
　　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　　SELECTSYNNAM, DBMS_JAVA.LONGNAME(SYNNAM), DBMS_JAVA.LONGNAME(SYNTAB), TABOWN,TABNODE, PUBLIC$, SYNOWN, SYNOWNID, TABOWNID, SYNOBJNO FROM SYS.EXU9PTSWHERE 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.
　　
　　Privilegesto 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 PUBLICrole.
　　按照文章的提示，执行grant execute on DBMS_JAVA to dba;语句之后exp操作恢复正常。
　　    出现这个问题的原因是PUBLIC的DBMS_JAVA包权限被回收，要想执行exp导出成功需要DBMS_JAVA的execute权限，然而担心到安全的问题，回收了DBMS_JAVA包的执行权限，以保证数据库的安全。
　　
　　    按照Oracle的解释，那么一定是某种不安全的操作触发了Oracle因为安全的原因回收了PUBLIC的DBMS_JAVA execute权限，从而导致的该报错。
　　
　　--end--