Showing posts with label view and drop database link. Show all posts
Showing posts with label view and drop database link. Show all posts

Thursday, March 12, 2015

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