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 errorsI 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
No comments:
Post a Comment