ORA-39127: unexpected error from call to status during Oracle expdp
BUG-000120893 - Exporting a geodatabase using the Oracle 12c R2 Data Pump utility returns ORA errors...
Issue Description:
When exporting an enterprise geodatabase in Oracle 12c R2 using the Data Pump utility, the following errors are observed in the export log:
ORA-39127: unexpected error from call to status := SYS.DBMS_EXPORT_EXTENSION.GET_V2_DOMAIN_INDEX_TABLES('A1_IX1','SDE','ST_DOMAIN_METHODS','SDE',0,'12.02.00.01.00',1,0)
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 511
ORA-06512: at "SYS.DBMS_ODCI", line 73
ORA-06512: at "SYS.DBMS_ODCI", line 279
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQL", line 1721
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 487
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 511
ORA-06512: at "SYS.DBMS_ODCI", line 73
ORA-06512: at "SYS.DBMS_ODCI", line 279
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQL", line 1721
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 487
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 10221
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
Workaround:
The following workflow serves to clean up the extra ST_DOMAIN_METHODS entries so that during the export the ORA errors are not returned.
However, despite the export errors, the generated dump file does contain the ST_DOMAIN_METHODS type and its dependent objects.
No issue is seen with importing the dump file to a new database, and the types and spatial indexes are created properly after the import.
Therefore, it is recommended to import the dump file first and see if it is really necessary to carry out the following workflow:
1. Drop the existing ST_GEOMETRY spatial indexes.
2. Get the ddl of the ST_SPATIAL_INDEX indextype: SELECT dbms_metadata.get_ddl('INDEXTYPE','ST_SPATIAL_INDEX','SDE') FROM DUAL;
3. Drop the existing ST_SPATIAL_INDEX indextype.
4. Drop the existing ST_DOMAIN_METHODS and ST_DOMAIN_STATS types.
5. Re-generate ST_DOMAIN_METHODS and ST_DOMAIN_STATS using the stock libraries that come with the ArcGIS Desktop install and grant the appropriate privileges.
Note:
By default, the libraries can be found in
ArcMap - C:\Program Files (x86)\ArcGIS\Desktop10.7\lib).
ArcGIS Pro - C:\Program Files\ArcGIS\Pro\Resources\DatabaseSupport\Oracle\plsql
To recreate the types:
SQLPLUS sde/<password>@<host>/<db_name>
start '<lib_directory>\st_domain_stats.sts' SDE
start '<lib_directory>\st_domain_stats.stb' SDE
start '<lib_directory>\st_domain_methods.sts' SDE
start '<lib_directory>\st_domain_methods.stb' SDE
grant EXECUTE on sde.ST_DOMAIN_METHODS to public;
grant EXECUTE on sde.ST_DOMAIN_STATS to public;
grant EXECUTE on sde.spx_util to public;
6. Re-create the ST_SPATIAL_INDEX indextype using the ddl obtained in step 2.
7. Grant appropriate privileges on the indextype: GRANT EXECUTE ON ST_SPATIAL_INDEX TO PUBLIC;
8. Re-create the ST_GEOMETRY spatial indexes.
