dba:oracle:scripts:recompile_it_all
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revision | Last revisionBoth sides next revision | ||
dba:oracle:recompile_it_all [2022/02/11 11:12] – ↷ Page moved from oracle:recompile_it_all to dba:oracle:recompile_it_all dodger | dba:oracle:scripts:recompile_it_all [2023/01/31 08:29] – removed - external edit (Unknown date) 127.0.0.1 | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ====== [SCRIPT] Recompile all database objects ====== | ||
- | |||
- | ====== 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 -- ################################# | ||
- | select owner, Object_type, | ||
- | WHERE OWNER NOT IN ( ' | ||
- | AND STATUS=' | ||
- | ; | ||
- | |||
- | |||
- | prompt -- ################################# | ||
- | BEGIN | ||
- | FOR oname IN ( | ||
- | SELECT | ||
- | owner, | ||
- | CASE WHEN Object_type LIKE '% BODY' THEN REPLACE(OBJECT_TYPE, | ||
- | FROM SYS.ALL_OBJECTS | ||
- | WHERE OWNER NOT IN ( ' | ||
- | AND STATUS=' | ||
- | AND OBJECT_TYPE LIKE '% BODY' | ||
- | ) | ||
- | LOOP | ||
- | BEGIN | ||
- | EXECUTE IMMEDIATE 'ALTER ' || oname.object_type || ' ' || oname.owner || ' | ||
- | EXCEPTION | ||
- | WHEN OTHERS THEN | ||
- | dbms_output.put_line(' | ||
- | END; | ||
- | END LOOP; | ||
- | |||
- | FOR oname IN ( | ||
- | SELECT | ||
- | owner, | ||
- | CASE WHEN Object_type LIKE '% BODY' THEN REPLACE(OBJECT_TYPE, | ||
- | FROM SYS.ALL_OBJECTS | ||
- | WHERE OWNER NOT IN ( ' | ||
- | AND STATUS=' | ||
- | ) | ||
- | LOOP | ||
- | BEGIN | ||
- | EXECUTE IMMEDIATE 'ALTER ' || oname.object_type || ' ' || oname.owner || ' | ||
- | EXCEPTION | ||
- | WHEN OTHERS THEN | ||
- | dbms_output.put_line(' | ||
- | END; | ||
- | END LOOP; | ||
- | END; | ||
- | / | ||
- | |||
- | prompt -- ################################# | ||
- | select count(*) INVALID_OBJECTS from dba_objects where owner=' | ||
- | </ | ||
dba/oracle/scripts/recompile_it_all.txt · Last modified: 2023/01/31 08:29 by dodger