Saturday, September 15, 2007

An Unusual Export Error


A full database export was completing with the following errors in one of our UAT database (Oracle 10g Release 2):

EXP-00008: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 257
ORA-06512: at line 1
EXP-00078: Error exporting metadata for index TB_SDN_EXT_IDX. Index creation will be skipped

Strange thing to note is the error message “ORA-01031: insufficient privileges” and the error is raised only for indexes of type “DOMIAN”. The user executing export has “EXP_FULL_DATABASE” and “IMP_FULL_DATABASE” roles granted.

We have been encountering these errors only after installing “Oracle Text” option, before installing “Oracle Text”, the exports were running successfully.

To resolve the problem we have to explicitly grant “SELECT ANY TABLE” privilege to the exporting user.

Well, it’s logical that when a user has “EXP_FULL_DATABASE” role granted there is no need to grant “SELECT ANY TABLE” privilege. But still, that solved our problem.


1 comment:

Helga said...

Indeed, the error looks strange. However, I have encountered a great number of strange errors in my practice. I usually apply the attach sql 2000 database file corrupted recovery program, it fixes even unusual errors