Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Thursday, December 11, 2014

ORA-01427 While running DBMS_HM.RUN_CHECK



SQL> BEGIN
  2      DBMS_HM.RUN_CHECK('Dictionary Integrity Check', 'HC1');
  3  END;
  4  /
BEGIN
*

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 2

select /*+ first_rows */
 owner, object_name, data_object_id
  from dba_objects
 where data_object_id in
       (select dataobj# from IND$ group by dataobj# having count(*) > 1)
       order by 3 ;

OWNER OBJECT_NAME DATA_OBJECT_ID OBJECT_TYPE
SYS SYS_C0011090
87367
INDEX
OE WHS_LOCATION_IX
87367
INDEX
SYS SYS_C0011093
87369
INDEX
OE ITEM_ORDER_IX
87369
INDEX
SYS SYS_IOT_TOP_87373
87374
INDEX
OE CUST_ACCOUNT_MANAGER_IX
87374
INDEX
OE CUST_EMAIL_IX
87376
INDEX
SYS SYS_IOT_TOP_87375
87376
INDEX

drop index sys.SYS_C0011090;
drop index oe.WHS_LOCATION_IX;
drop index sys.SYS_C0011093;
drop index oe.ITEM_ORDER_IX;
drop index sys.SYS_IOT_TOP_87373;
drop index oe.CUST_ACCOUNT_MANAGER_IX;
drop index oe.CUST_EMAIL_IX;
drop index oe.SYS_IOT_TOP_87375;


SQL> BEGIN
  2      DBMS_HM.RUN_CHECK('Dictionary Integrity Check', 'HC2');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>


No comments: