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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
