solution :- constraint (SYS.I_SOURCE1) violated

I am trying to drop an object from oracle using sqlplus then meanwhile i am cancelling the operation .
After i am drop the same object then  raise the error  that object does not exist.


And after i am creating the same object with some modification  then getting the error
constraint (SYS.I_SOURCE1)  violated  .

solution

find any object are not in user and existing in SYS.SOURCE$

select * from SYS.SOURCE$ where SOURCE  like '%OBJECTNAME%';

select OBJECT_NAME,OBJECT_ID from all_objects where owner='USER' and object_type='TYPE';

once you confirm which object are missing in user and existing in sys the you can delete the entries from sys.



    error : unique constraint (SYS.I_SOURCE1) violated

  delete from SYS.SOURCE$  where OBJ#=74615 and line=1;
    
     error : unique constraint (SYS.I_IDL_SB41) violated

    delete from  SYS.IDL_SB4$  where OBJ#=74615;
   
    error : unique constraint (SYS.I_IDL_UB11) violated
   
    delete from  SYS.IDL_UB1$  where OBJ#=74615;

    error : unique constraint (SYS.I_IDL_CHAR1) violated

    delete from  SYS.IDL_CHAR$ where OBJ#=74615;

    error : unique constraint (SYS.I_IDL_UB21) violated

   delete from   SYS.IDL_UB2$  where OBJ#=74615;

ORA-48223: Interrupt Requested - Fetch Aborted - Return Code [51111] [HM_INFO]

ORA-48223: Interrupt Requested - Fetch Aborted - Return Code [51111] [HM_INFO]

ORA-51108: unable to access diagnostic repository - retry command
ORA-48223: Interrupt Requested - Fetch Aborted - Return Code [51111] [HM_INFO]
ORA-06512: at "SYS.DBMS_IR", line 522

ORA-12012: error on auto execute of job "SYS"."DRA_REEVALUATE_OPEN_FAILURES"

ORA-51108: unable to access diagnostic repository - retry command



If you face this type of errors then you can proceed the following steps

SQL> exec dbms_hm.drop_schema;

PL/SQL procedure successfully completed.

SQL> exec dbms_hm.create_schema;

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.