ORA-39127, ORA-01422, and ORA-06512 warning messages appear in Oracle Data Pump export logs when exporting an enterprise geodatabase

Description

When using the Oracle Data Pump (EXPDP) utility, ORA‑39127, ORA‑01422, and ORA‑06512 messages may be reported in the export log in association with the SDE.ST_DOMAIN_METHODS type. These messages are warning-level notifications and do not affect the integrity or functionality of the data contained in the dump file when it is imported into an Oracle database.

An example of the reported warning messages is shown below:

C:\> EXPDP system/manager@SERVER1/ORCL directory=DPDIR dumpfile=EXPDP_FULL.dmp 
logfile=EXPDP_FULL.log full=y job_name=EXPDP_FULL

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,'19.05.00.00.00',1,0)
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 520
ORA-06512: at "SYS.DBMS_ODCI", line 82
ORA-06512: at "SYS.DBMS_ODCI", line 301
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQL", line 1721
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 496
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 520
ORA-06512: at "SYS.DBMS_ODCI", line 82
ORA-06512: at "SYS.DBMS_ODCI", line 301
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQL", line 1721
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 496
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 10658
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

Cause

These messages occur because, after upgrading an enterprise geodatabase in Oracle, the properties of the SDE.ST_DOMAIN_METHODS type may change. When this occurs, Oracle records an additional entry for the updated SDE.ST_DOMAIN_METHODS type in the SYS.OBJ$ data dictionary table to reflect the change for that ArcGIS release. Over time, successive enterprise geodatabase upgrades can result in the accumulation of these records in the SYS.OBJ$ table.

To determine whether your SYS.OBJ$ table contains records associated with evolved changes to the SDE.ST_DOMAIN_METHODS type, execute the following sql query. If the query returns more than two records - one for the TYPE and one for the TYPE BODY – type evolution changes are present for the SDE.ST_DOMAIN_METHODS type.

The following example, the query returns seven records associated with the TYPE and TYPE BODY objects:

SQL> SELECT object_name, object_type
FROM sys.dba_objects
WHERE owner = 'SDE'
 AND object_name = 'ST_DOMAIN_METHODS'
ORDER BY 1;

OBJECT_NAME          OBJECT_TYPE
-------------------- -----------------------
ST_DOMAIN_METHODS    TYPE
ST_DOMAIN_METHODS    TYPE
ST_DOMAIN_METHODS    TYPE
ST_DOMAIN_METHODS    TYPE
ST_DOMAIN_METHODS    TYPE
ST_DOMAIN_METHODS    TYPE
ST_DOMAIN_METHODS    TYPE BODY

7 rows selected.

SQL> SELECT o.obj#, o.name
FROM sys.obj$ o, sys.dba_users u
WHERE o.owner# = u.user_id
 AND u.username = 'SDE'
 AND o.name = 'ST_DOMAIN_METHODS'
ORDER BY 1,2;

     OBJ#  name
---------- ------------------------------
    47326  ST_DOMAIN_METHODS
    47327  ST_DOMAIN_METHODS
    47328  ST_DOMAIN_METHODS
    47329  ST_DOMAIN_METHODS
    47330  ST_DOMAIN_METHODS
    47331  ST_DOMAIN_METHODS
    47507  ST_DOMAIN_METHODS

7 rows selected.

Workaround

To remove these additional records from the SYS.OBJ$ data dictionary table, execute the following sql query:

SQL> alter type SDE.ST_DOMAIN_METHODS reset;
Type altered

After resetting the type, the related objects will become INVALID.

SQL> SELECT object_name, object_type, status
FROM sys.dba_objects
WHERE owner = 'SDE'
 AND object_name = 'ST_DOMAIN_METHODS'
ORDER BY 1;

OBJECT_NAME          OBJECT_TYPE             STATUS
-------------------- ----------------------- -------
ST_DOMAIN_METHODS    TYPE                    VALID
ST_DOMAIN_METHODS    TYPE BODY               INVALID

2 rows selected.

SQL> SELECT owner, object_name, status
FROM sys.dba_objects
WHERE owner = 'SDE'
 AND status <> 'VALID';

OWNER            OBJECT_NAME          STATUS
---------------- -------------------- -------
SDE              ST_DOMAIN_METHODS    INVALID
SDE              ST_SPATIAL_INDEX     INVALID
SDE              A1_IX1               INVALID

3 rows selected.

Next, recompile the SDE schema to fix these INVALID objects.

SQL> EXECUTE sys.utl_recomp.recomp_serial('SDE');

PL/SQL procedure successfully completed.

Use the following sql query to confirm the objects are VALID.

SQL> SELECT object_name, object_type, status
FROM sys.dba_objects
WHERE owner = 'SDE'
 AND object_name = 'ST_DOMAIN_METHODS'
ORDER BY 1;

OBJECT_NAME          OBJECT_TYPE             STATUS
-------------------- ----------------------- -------
ST_DOMAIN_METHODS    TYPE                    VALID
ST_DOMAIN_METHODS    TYPE BODY               VALID

2 rows selected.

SQL> SELECT owner, object_name, status
FROM dba_objects
WHERE owner = 'SDE'
 AND status <> 'VALID';

no rows selected

As a final step, confirm that the additional records in the SYS.OBJ$ data dictionary table have been successfully removed.

SQL> SELECT o.obj#, o.name
FROM sys.obj$ o, sys.dba_users u
WHERE o.owner# = u.user_id
 AND u.username = 'SDE'
 AND o.name = 'ST_DOMAIN_METHODS'
ORDER BY 1,2;

     OBJ#  NAME
---------- ------------------------------
    47331  ST_DOMAIN_METHODS
    47507  ST_DOMAIN_METHODS

2 rows selected.

SQL> SELECT object_name, object_type
FROM sys.dba_objects
WHERE owner = 'SDE'
 AND object_name = 'ST_DOMAIN_METHODS'
ORDER BY 1,2;

OBJECT_NAME          OBJECT_TYPE
-------------------- -----------------------
ST_DOMAIN_METHODS    TYPE
ST_DOMAIN_METHODS    TYPE BODY

2 rows selected.

Subsequent exports performed using the Oracle Data Pump (EXPDP) utility will no longer report ORA-39127, ORA-01422, and ORA-06512 warning messages.