Compiling PL/SQL

Submitted by dave on Wed, 04/07/2010 - 17:17

Generate Statements to Compile Invalid Objects

SELECT 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' COMPILE;' as SQL
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY 1;

Complete Recompile
1) Compile all types

2) Upgrade Tables to Sync Types
For all tables, run ALTER TABLE UPGRADE;
This incorporates the latest version of the types into the tables.

3) Run UTLIRP.SQL
This invalidates all SQL

4) Restart DB, run UTLRP.SQL

Status queries:

select object_type, status, count(*) from dba_objects where status='INVALID' group by object_type, status order by 1,2;

SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

WITH ObjTypes as (SELECT DISTINCT OBJECT_TYPE FROM DBA_OBJECTS),
ObjSummary as (SELECT OBJECT_TYPE, STATUS, COUNT(*) as Cnt FROM DBA_OBJECTS GROUP BY OBJECT_TYPE, STATUS)
SELECT OBJECT_TYPE,
(SELECT Cnt FROM ObjSummary WHERE OBJECT_TYPE=ObjTypes.OBJECT_TYPE AND STATUS='VALID') as Valid,
(SELECT Cnt FROM ObjSummary WHERE OBJECT_TYPE=ObjTypes.OBJECT_TYPE AND STATUS='INVALID') as InValid
FROM ObjTypes ORDER BY 1;