ORA-01422 from DBMS_XS_PRINCIPAL_EVENTS_INT DBA|ALL|USER_XSC_* and DBA|ALL|USER_XDS_*

(Doc ID 1332182.1)

Last updated on APRIL 16, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.7 and later
Information in this document applies to any platform.

Symptoms

DBA_REGISTRY shows Oracle XML Database (XDB) is INVALID.

DBA_OBJECTS shows a number of INVALID objects including DBA|ALL|USER_XSC_* and DBA|ALL|USER_XDS_*:

OWNER                OBJECT_NAME                    OBJECT_TYPE
-------------------- ------------------------------ -------------------
...
PUBLIC               ALL_XDS_ATTRIBUTE_SECS         SYNONYM
PUBLIC               ALL_XDS_INSTANCE_SETS          SYNONYM
PUBLIC               ALL_XSC_AGGREGATE_PRIVILEGE    SYNONYM
PUBLIC               ALL_XSC_PRIVILEGE              SYNONYM
PUBLIC               ALL_XSC_SECURITY_CLASS         SYNONYM
PUBLIC               ALL_XSC_SECURITY_CLASS_DEP     SYNONYM
PUBLIC               DBA_NETWORK_ACL_PRIVILEGES     SYNONYM
PUBLIC               DBA_XDS_ATTRIBUTE_SECS         SYNONYM
PUBLIC               DBA_XDS_INSTANCE_SETS          SYNONYM
PUBLIC               DOCUMENT_LINKS                 SYNONYM
PUBLIC               USER_XDS_ATTRIBUTE_SECS        SYNONYM
PUBLIC               USER_XDS_INSTANCE_SETS         SYNONYM
PUBLIC               XDS_ACE                        SYNONYM
PUBLIC               XDS_ACL                        SYNONYM
SYS                  ALL_XDS_ATTRIBUTE_SECS         VIEW
SYS                  ALL_XDS_INSTANCE_SETS          VIEW
SYS                  ALL_XSC_AGGREGATE_PRIVILEGE    VIEW
SYS                  ALL_XSC_PRIVILEGE              VIEW
SYS                  ALL_XSC_SECURITY_CLASS         VIEW
SYS                  ALL_XSC_SECURITY_CLASS_DEP     VIEW
SYS                  DBA_NETWORK_ACL_PRIVILEGES     VIEW
SYS                  DBA_XDS_ATTRIBUTE_SECS         VIEW
SYS                  DBA_XDS_INSTANCE_SETS          VIEW
SYS                  DBMS_XS_PRINCIPAL_EVENTS_INT   PACKAGE BODY
SYS                  USER_XDS_ATTRIBUTE_SECS        VIEW
SYS                  USER_XDS_INSTANCE_SETS         VIEW
SYS                  XDS_ACE                        VIEW
SYS                  XDS_ACL                        VIEW
XDB                  DOCUMENT_LINKS                 VIEW


Running utlrp.sql to recompile the INVALID objects does not help. Querying utl_recomp_errors shows the compilation of the INVALID objects fail with an ORA-01422:

SQL> select obj#,error_at,compile_err from utl_recomp_errors;

OBJ#    ERROR_AT
------- ----------------------------
COMPILE_ERR
-------------------------------------------------------------------------------------
369555  17-JUN-11 07.39.37.055365 PM
ORA-04045: errors during recompilation/revalidation of SYS.DBA_NETWORK_ACL_PRIVILEGES
ORA-01422: exact fetch returns more than requested number of rows

369579  17-JUN-11 07.39.42.475663 PM
ORA-04045: errors during recompilation/revalidation of SYS.ALL_XSC_SECURITY_CLASS
ORA-01422: exact fetch returns more than requested number of rows

369556  17-JUN-11 07.39.43.314695 PM
ORA-04045: errors during recompilation/revalidation of SYS.DBA_NETWORK_ACL_PRIVILEGES
ORA-01422: exact fetch returns more than requested number of rows

369580 17-JUN-11 07.39.48.171049 PM
ORA-04045: errors during recompilation/revalidation of SYS.ALL_XSC_SECURITY_CLASS
ORA-01422: exact fetch returns more than requested number of rows


Attempting to recompile the objects manually show the same error:

SQL> alter view xdb.DOCUMENT_LINKS compile;

alter view xdb.DOCUMENT_LINKS compile
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level
ORA-01422: exact fetch returns more than requested number of rows

Changes

This had been seen to occur on installations where Oracle Applications is also installed.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms