Fix Views That Stop Working

A view is based on query against other database objects that may involve tables, views, database links, etc.. Sometimes when the underline objects changes, the view may become invalid. For example, a view is defined based on a remote table. When I dropped and recreate the database link pointing to the remote table, the querying against the view returns error.

SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 30 10:09:48 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL>  select count(*) from V_CLAIM
old   1: select count(*) from &1
new   1: select count(*) from V_CLAIM
select count(*) from V_CLAIM
                     *
ERROR at line 1:
ORA-04063: view "PROD.V_CLAIM" has errors

I use dbms_utility.invalidate procedure to "fix" the view as shown below. First, we need to find the object_id for the view.

SQL> select object_name, object_id from user_objects where object_name='V_CLAIM';
OBJECT_NAME                               OBJECT_ID
---------------------------------------- ----------
V_CLAIM                                   16995

SQL> exec dbms_utility.invalidate(16995);

PL/SQL procedure successfully completed.

SQL> @ct V_SH_FH_CLAIM
old   1: select count(*) from &1
new   1: select count(*) from V_CLAIM

  COUNT(*)
----------
     24782

Create Views Based on Nonexistent Tables

In Oracle, it is possible to create views first based on nonexistent tables. After views are created, we can create the underlying tables and the view will work.
To create a view based on tables that do not exist, we using "create force view" as shown below.

SQL> create force view v_tbla as select * from tbla;
Warning: View created with compilation errors.

It is OK that we got the compilation errors. The key word "force" is necessary. Without it, the view will not be created.

SQL> create view v_tbla as select * from tbla;
create view v_tbla as select * from tbla
                                    *
ERROR at line 1:
ORA-00942: table or view does not exist

If we query this view, we will get an error message. This is fine. The error message will disappear after we create the underlying table tbla.

SQL> select * from v_tbla;
select * from v_tbla
              *
ERROR at line 1:
ORA-04063: view "DMUSER.V_TBLA" has errors

Now we create table that the view is based on and populate it with data. As we see, we can query the view!

SQL> create table tbla (id number, val varchar2(32));
Table created.

SQL> insert into tbla values (1,'hello');
1 row created.

SQL> select * from tbla;

 ID VAL
---------- --------------------------------
  1 hello

SQL> select * from v_tbla;

 ID VAL
---------- --------------------------------
  1 hello