Re: invalid views

From: Rumpi Gravenstein <rgravens_at_gmail.com>
Date: Tue, 26 Sep 2006 13:01:54 -0400
Message-ID: <9c9b9dc90609261001o74eaa05di7c5dd346c4c3d15c@mail.gmail.com>

A good way to track this down is to put a DDL trigger on all the objects referenced by the views that are going invalid. The trigger, along with the last ddl time in the objects view can be used to track what is causing the invalidations.

Here's one way of doing that if all the objects are in the same schema:

create table ddl_log
(

  user_name   varchar2(30) NOT NULL,
  ddl_date    date         NOT NULL,
  ddl_type    varchar2(30) NOT NULL,
  object_type varchar2(30) NOT NULL,
  owner       varchar2(30) NOT NULL,
  object_name varchar2(30) NOT NULL,

ip_address varchar2(30)
)
tablespace USERS
/

CREATE OR REPLACE TRIGGER DDLTRIGGER
  AFTER DDL
  ON SCHEMA
DECLARE
   p_address VARCHAR2(30);
BEGIN

Collect terminal information SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') || ' ' || SYS_CONTEXT('USERENV', 'TERMINAL') INTO p_address FROM dual;
Record change INSERT INTO ddl_log (user_name, ddl_date, ddl_type, object_type, owner, object_name, ip_address ) VALUES ( ora_login_user, SYSDATE, ora_sysevent, ora_dict_obj_type, ora_dict_obj_owner, ora_dict_obj_name, p_address ); END; /

On 9/22/06, Thomas Day <tomday2_at_gmail.com> wrote:
>
> How is your developer fixing this problem? I've had views go invalid in
> the past, but if the base table(s) were unchanged, doing a select on the
> view always changed its status to valid.
>
>
>
> SQL> create table gorp (gorp1 number);
>
> Table created.
>
> SQL> insert into gorp values (5);
>
> 1 row created.
>
> SQL> create view gorp5 as (select * from gorp where gorp1 = 5);
>
> View created.
>
> SQL> select * from gorp5;
>
> GORP1
> ----------
> 5
>
> 1 row selected.
>
> SQL> rename gorp to prog;
>
> Table renamed.
>
> SQL> select * from gorp5;
> select * from gorp5
> *
> ERROR at line 1:
> ORA-04063: view "GORP5" has errors
>
>
> SQL> select object_name , object_type, status from dba_objects where
> object_name = 'GORP5';
>
> OBJECT_NAME
>
> --------------------------------------------------------------------------------
> OBJECT_TYPE STATUS
> ------------------ -------
> GORP5
> VIEW INVALID
>
>
> 1 row selected.
>
> SQL> RENAME PROG TO GORP;
>
> Table renamed.
>
> SQL> select object_name , object_type, status from dba_objects where
> object_name = 'GORP5';
>
> OBJECT_NAME
>
> --------------------------------------------------------------------------------
> OBJECT_TYPE STATUS
> ------------------ -------
> GORP5
> VIEW INVALID
>
>
> 1 row selected.
>
> SQL> select * from gorp5;
>
> GORP1
> ----------
> 5
>
> 1 row selected.
>

-- 
Rumpi Gravenstein

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 26 2006 - 12:01:54 CDT