Jump to Navigation

170 - Invalid object & compile script

1. Find invalid objects by SQL & Compile Command.

COLUMN object_name FORMAT A30
SELECT owner, object_type, object_name, status FROM dba_objects WHERE  status = 'INVALID' ORDER BY owner, object_type, object_name;

ALTER PACKAGE APPS.XXX_UNPROCESSED_DATA_REPORT COMPILE;
ALTER TRIGGER APPS.XXX_PAYMENT_BATCH_TBL_UPD COMPILE;

2. Compile Script for a lot of invalid objects

If there are many invalid objects, the following script might be useful.

set heading off;
set feedback off;
set echo off;
eet lines 999;
 
Spool run_invalid.sql
 
select
   'ALTER ' || OBJECT_TYPE || ' ' ||
   OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from
   dba_objects
where
   status = 'INVALID'
and
   object_type in ('PACKAGE','FUNCTION','PROCEDURE')
;
 
spool off;
 
set heading on;
set feedback on;
set echo on;
 
@run_invalid.sql

 

Oracle:


Main menu 2

Story | by Dr. Radut