Ghost Views ;)

Recently I was reported of a problem that some views were getting invalid regularly in the database. I used to compile it and on the next day i found that they were invalid again. Though there was no problem with the invalid views as they would be compiled when they would be used in a query.

I tried to find out the tables used in those views through the view dba_dependencies and whether any of the table used in the views was altered in the night which rendered my views invalid. I found that a batch would truncate one of the table and populate it again, but truncating does not alter the structure of the view so the dependent objects would not become invalid.

Later I found that before truncating the table the index (both unique and non unique ) were dropped and after the table was populated the indexes were created again. So there lied my problem.

Thanks to Saubhik for helping me out.

The dropping of unique index invalidated all objects dependent on that particular table, So dropping a normal index would not invalidate any dependent objects.  Here is a demo of the problem.

I create a table from the all_objects table and created a unique index on the column object_id. I also create a force view referencing my table my_objects.

SQL> create table my_objects as select * from all_objects;

SQL> create unique index uniq_indx_my_objects on my_objects(object_id);

Index created.


SQL> create or replace force view v_my_object(owner,object_name,object_id) as
 2  select owner,object_name,object_id from my_objects;

View created.

I checked the status and the dependency of the view.

SQL> select object_name,object_type,status from all_objects where object_name like '%V_MY_OBJECT%';

OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
V_MY_OBJECT                    VIEW                VALID


SQL>  select * from dba_dependencies where name ='V_MY_OBJECT';

OWNER                          NAME                           TYPE              REFERENCED_OWNER               REFERENCED_NAME
------------------------------ ------------------------------ ----------------- ------------------------------ ---------------------
SCOTT                          V_MY_OBJECT                    VIEW              SCOTT                          MY_OBJECTS

Next Step was to drop the index and look for the status of the view.

SQL> drop index  uniq_indx_my_objects;

Index dropped.

SQL> select object_name,object_type,status from all_objects where object_name like '%V_MY_OBJECT%';

OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
V_MY_OBJECT                    VIEW                INVALID

Finally the index was recreated and the view was compiled.

SQL>  create unique index uniq_indx_my_objects on my_objects(object_id);

Index created.


SQL> alter view V_MY_OBJECT compile;

View altered.

SQL>  select object_name,object_type,status from all_objects where object_name like '%V_MY_OBJECT%';

OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
V_MY_OBJECT                    VIEW                VALID

So now every time my batches run I make sure the i run the compile all utility to validate all the invalid objects. 🙂

Advertisement