September 25, 2010

Invalidation of dependent code objects for dropping an index on the base table?

Filed under: Uncategorized — srivenu @ 10:21 am

“When you drop an index, Oracle invalidates all objects that depend on the underlying table, including views, packages, package bodies, functions, and procedures.”

This statement has been part of Oracle documentation since 9i doc (At least it wasn’t there in the same section in 8i doc)

I never thought for a minute that dropping an index would invalidate the code objects dependant on the underlying base table. That was till a couple of days back !

As part of tuning some sql, i was creating new indexes ones and dropping some old ones. All the index changes were on look-up tables. Even though it was on a production system, I thought that it wouldn’t impact anyone as the tables were small & static. How wrong & ignorant i was !

Next day i had been told that the end-users experienced an application freeze for around 6 minutes. On analysis it was found that the database faced several latch free events on shared pool & library cache and library cache pins. It was also noted that several code objects got invalid during that time and had undergone implicit recompilation. We had a DDL trigger at database level to capture all DDL changes. And the only DDL’s that we saw prior to the impact was the INDEX DROP. Some of my colleagues suggested that the INDEX drop has invalidated the procedures. I immediately rubbished their claim that an index drop would cause object invalidations.

To find the culprit, i decided to do some log mining. I was searching for the details of the session which invalidated the procs. I looked for SQL_REDO where the STATUS column in the OBJ$ table for the PROC object (OBJ#) was updated to a status of 5(invalid). I found out the session and after further analysis, i found that it was my session! I looked at all the SQL_REDO’s of my session and found that it had first updated the status of all the dependent procedures to an INVALID STATUS before executing the DROP INDEX statement. I immediately ran a quick test.

Create table X(a number);

Create index ix on X(a);

Create or replace procedure PX as
delete from X where a=10;

I dropped the index IX and checked for the status of PX. It was still VALID

On close observation i found out that the index i dropped during the issue was a FUNCTION-BASED index. So i repeated the test with a function-based index

Create table X(a number);

Create index ix on X(to_char(a));

Create or replace procedure PX as
delete from X where a=10;

I dropped the index IX and checked for the status of PX. It was now INVALID

So dependent code objects on a base table are only invalidated if you drop a Function-Based index on that table. (I did not check for other types). I raised an SR for fixing the documentation.

But this left me pondering the reason for this invalidation !

As a commentor has pointed out, the invalidation is no longer happening in 11g. I had infact raised a SR to get the Oracle documentation fixed
(SR 3-2143692451)



  1. What is described is correct for function-based indexes in Release 10R2. It has been corrected in Release 11 (tested in and

    Comment by JMAVR — November 21, 2010 @ 11:14 pm | Reply

    • Yes the invalidation is not happening in 11g. I had infact raised a SR for fixing the Oracle doc.

      Comment by ksrivenu — November 22, 2010 @ 5:20 am | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog at

%d bloggers like this: