Oracle编译无效对象;删除所有对象;

 

 1 create or replace procedure compile_invalid_obj 
 2 as
 3     /*
 4      * 功能：编译当前用户下的无效 存储过程、视图、函数
 5      * zhangxd 2009-9-7
 6      */
 7     str_sql varchar2(200);
 8     v_count integer;
 9 begin
10     v_count := 1;
11     dbms_output.put_line('----compile invalid obj begin----');
12     for invalid_procedures in
13         (
14              SELECT object_name,object_type FROM USER_OBJECTS
15               WHERE status = 'INVALID'
16                 AND object_type in ('FUNCTION','PROCEDURE','VIEW')
17         )
18     loop
19         str_sql := 'alter '||invalid_procedures.object_type||' '||invalid_procedures.object_name||' compile';
20         begin
21             dbms_output.put_line(v_count || '->sql : '||str_sql);
22             execute immediate str_sql;
23             dbms_output.put_line('                  compile success!');
24         exception
25           --When Others Then Null;
26             when OTHERS Then
27             dbms_output.put_line('                  compile error : '||sqlerrm);
28         end;
29         v_count := v_count + 1;
30     end loop;
31     dbms_output.put_line('----compile invalid obj end----');
32 end;