Re: invalid views
- From: "Rumpi Gravenstein" <rgravens@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 26 Sep 2006 13:01:54 -0400
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@xxxxxxxxx> 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
Other related posts:
