Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Dropping index invalidates packages

hurtnJun 3 2010 — edited Mar 11 2013
We have a custom ETL solution written in PLSQL. We were running fine on 9204 and have now upgraded our test warehouse DB to 10204. To avoid overhead we drop indexes, do the load, and then recreate them after. The problem we are facing is that after moving to 10g, everytime we drop an index it invalidates a package that might use the associated table resulting in ORA-06508: PL/SQL: could not find program unit being called as soon as the invalid package is then invoked. To me this seems very strange as according to the docs this should only happen if we have a view on the table, see http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10743/depend.htm#sthref1112 which we definitely do not have.
As a work-around we are marking the index as unsuable before the load and then rebuilding after, but then of course one has to make all indexes non-unique in order for the skip_unused_indexes paramater to work... phew! Seems like a lot of effort for such a simple, common problem. Have we missed something? Any light on the matter would be appreciated.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 8 2013
Added on Jun 3 2010
8 comments
7,015 views