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$.
Subscribe to:
Post Comments (Atom)
1 comment:
I am having the same problem also on a regular basis. Sometimes it helps to drop the package or procedure and then restart the database.
Oracle seems to have added a feature that allows re-use of an object#. Good to avoid fragmentation of SYS.
When restarting the database finally get's lost.
It seems to have something to do with references not leading to actually allowing reuse of the correct object#.
Post a Comment