ORA-01427 occurs when running Dictionary Integrity Check: dbms_hm.run_check (Doc ID 1410513.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.0 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.

Symptoms

SQL> exec dbms_hm.run_check('Dictionary Integrity Check', 'Dict_check1');
BEGIN dbms_hm.run_check('Dictionary Integrity Check', 'Dict_check1'); END;

*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01427: single-row subquery returns more than one row
ORA-06512: at "SYS.DBMS_HM", line 191
ORA-06512: at line 1

 

-- Enable event
SQL> alter session set events '1427 trace name ERRORSTACK level 3';

-- Re-execute the failing command
SQL> exec dbms_hm.run_check('Dictionary Integrity Check', 'Dict_check1');

-- Disable event
SQL> alter session set events '1427 trace name ERRORSTACK off';

NOTE
----
  This will create a trace file on ORA-01427 occurrence.
  Check the location specified by background_dump_dest for the resultant trace file.

 

The trace file will confirm the following "Current SQL Statement":
TRACE FILE
----------

----- Error Stack Dump -----
ORA-01427: single-row subquery returns more than one row
----- Current SQL Statement for this session (sql_id=fh8zcx7dn0y1b) -----
select 134, rowid, 'icoldep$ pk' from ICOLDEP$ where obj# is null OR intcol# is null union all select 134, rowid, 'icoldep$ pk' from ICOLDEP$ where 1 > (select obj#||','||intcol# from ICOLDEP$ group by obj#||','||intcol# having count(*) > 1) union all select 135, rowid, 'icoldep$.obj# fk' from ICOLDEP$ where (obj#) in (select obj# from ICOLDEP$ where (obj#) not in (select obj# from ind$))
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0xce653508 191 package body SYS.DBMS_HM
0xc5dba448 1 anonymous block

 

Changes

The issue occurs whenever a Function-based Index is involved to define conditional uniqueness such as when using a "CASE WHEN ... THEN ..." construct:

create unique index {index_name} on {table_name}
   ...
   (CASE WHEN ... THEN ... END, ...)


Oracle Application Express (APEX)  version 3.x is using such "CASE WHEN ... THEN" constructs on several unique indexes.

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