Wednesday, January 25, 2012

Oracle Unique Constraint sys.i_procedure1 violated while creating package

Recently I faced issue in Oracle 11g, where after dropping a package, I was not able to recreate it. Recreating package was giving error, Unique Constraint (sys.i_procedure1) violated.

It was an issue with Oracle Dictionary table not getting updated while dropping package.

DBAs had to take following steps to resolve the issue.

select * from obj$ where name = 'PACK1' ;

select * from user$ where name = 'ABC' ; --This query is to get owner#, which can be joined with obj$

After getting obj#, we had to delete that object from 3 tables.

procedure$, source$ and obj$.