User Tools

Site Tools


oracle:recompile_it_all

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

oracle:recompile_it_all [2018/04/17 08:43]
dodger ↷ Page moved from recompile_it_all to oracle:recompile_it_all
oracle:recompile_it_all [2019/07/18 07:17]
Line 1: Line 1:
-====== Description ====== 
- 
- 
-How to compile the invalid objects 
- 
- 
-====== Instructions ====== 
- 
- 
-Execute the following block code as sys: 
- 
- 
-<code sql> 
-set echo off 
-set feed off 
- 
- 
-set linesize 200 
-col owner format a35 
-col Object_type format a35 
-col object_name format a35 
-prompt -- ################################# ​ INVALID_OBJECTS 
-select owner, Object_type,​ object_name from dba_objects 
-WHERE OWNER NOT IN ( '​SYSMAN',​ '​CTXSYS',​ '​MGMT_VIEW',​ '​TOOLS',​ '​SQLTXPLAIN',​ '​SYS',​ '​SYSTEM',​ '​DBSNMP'​ ) 
-AND STATUS='​INVALID'​ 
-; 
- 
- 
-prompt -- ################################# ​ COMPILING 
-BEGIN 
-    FOR oname IN ( 
-        SELECT ​ object_name,​ 
-                owner, 
-                CASE WHEN Object_type LIKE '% BODY' THEN REPLACE(OBJECT_TYPE,​ ' BODY', ''​) ELSE Object_type END AS Object_type 
-        FROM SYS.ALL_OBJECTS 
-        WHERE OWNER NOT IN ( '​SYSMAN',​ '​CTXSYS',​ '​MGMT_VIEW',​ '​TOOLS',​ '​SQLTXPLAIN',​ '​SYS',​ '​SYSTEM',​ '​DBSNMP'​ ) 
-        AND STATUS='​INVALID'​ 
-        AND OBJECT_TYPE LIKE '% BODY' 
-        ) 
-    LOOP 
-        BEGIN 
-            EXECUTE IMMEDIATE 'ALTER ' || oname.object_type || ' ' || oname.owner || '​.'​ || oname.object_name || ' COMPILE BODY'; 
-        EXCEPTION 
-            WHEN OTHERS THEN 
-                dbms_output.put_line('​Error compiling ' || oname.object_type || ' BODY ' || oname.owner || '​.'​ || oname.object_name);​ 
-        END; 
-    END LOOP; 
- 
-    FOR oname IN ( 
-        SELECT ​ object_name,​ 
-                owner, 
-                CASE WHEN Object_type LIKE '% BODY' THEN REPLACE(OBJECT_TYPE,​ ' BODY', ''​) ELSE Object_type END AS Object_type 
-        FROM SYS.ALL_OBJECTS 
-        WHERE OWNER NOT IN ( '​SYSMAN',​ '​CTXSYS',​ '​MGMT_VIEW',​ '​TOOLS',​ '​SQLTXPLAIN',​ '​SYS',​ '​SYSTEM',​ '​DBSNMP'​ ) 
-        AND STATUS='​INVALID'​ 
-        ) 
-    LOOP 
-        BEGIN 
-            EXECUTE IMMEDIATE 'ALTER ' || oname.object_type || ' ' || oname.owner || '​.'​ || oname.object_name || ' COMPILE '; 
-        EXCEPTION 
-            WHEN OTHERS THEN 
-                dbms_output.put_line('​Error compiling ' || oname.object_type || ' ' || oname.owner || '​.'​ || oname.object_name);​ 
-        END; 
-    END LOOP; 
-END; 
-/ 
- 
-prompt -- ################################# ​ INVALID_OBJECTS 
-select count(*) INVALID_OBJECTS from dba_objects where owner='​VOXEL'​ AND STATUS='​INVALID'​ ; 
-</​code>​ 
  
oracle/recompile_it_all.txt · Last modified: 2019/07/18 07:17 (external edit)