RE: Invalid views

From: Kevin Lange <kgel_at_ppoone.com>
Date: Thu, 14 Jun 2001 08:24:38 -0700
Message-ID: <F001.003297D1.20010614074708@fatcity.com>

Perfect timing. I was putting togeter some recompile scripts for work. Here is a modified one that can be used for views. With a few changes it can be used to recompile any invalid object.

1. Create a cursor on the Catalog Table ALL_OBJECTS where OBJECT_TYPE = 'VIEW' and STATUS = 'INVALID'.
2. Step thru that Cursor and use the DBMS_SQL calls to compile.

  create or replace Procedure Recompile_Views Is     Cursor cur_objects Is

      Select owner      ,
             object_name
      From dba_objects
      Where object_type = 'VIEW'
        and status = 'INVALID';

    current_cursor  Integer;
    dummy_var       Integer;

    cur_objects_rec cur_objects%ROWTYPE;

    Begin

      dbms_output.enable(20000);
      For cur_objects_rec In cur_objects
      Loop
 

dbms_output.put_line(cur_objects_rec.owner||'.'||cur_objects_rec.object_name );

        Begin
          current_cursor := DBMS_SQL.OPEN_CURSOR;
          DBMS_SQL.PARSE (current_cursor, 'ALTER VIEW

'||cur_objects_rec.owner||'.'||cur_objects_rec.object_name||' compile',DBMS_SQL.NATIVE);

          dummy_var := DBMS_SQL.EXECUTE (current_cursor);
          DBMS_SQL.CLOSE_CURSOR(current_cursor);
        Exception
          When Others
          Then
            DBMS_SQL.CLOSE_CURSOR(current_cursor);
            dbms_output.put_line('Unable to compile
'||cur_objects_rec.owner||'.'||cur_objects_rec.object_name);
        End;
      End Loop;

    End;

This code does work, you just have to make sure you have the correct authority on DBA_OBJECTS. Make sure the compile is granted select directly on DBA_OBJECTS by SYS.

Kevin

-----Original Message-----
Sent: Thursday, June 14, 2001 2:36 AM
To: Multiple recipients of list ORACLE-L

Hallo all you DBA's

Can anyone help me with this?

I would like to check if some views ar einvalid and then if there are I would like them to be compiled immediately .  How can I write this in a pl/sql-procedure? Please give me some good examples, all you experts!

Roland Sköldblom

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Roland.Skoldblom_at_ica.se

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kevin Lange
  INET: kgel_at_ppoone.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Thu Jun 14 2001 - 10:24:38 CDT