Useful Scripts to diagnose invalids

In this post, I will share some useful queries to diagnose invalids in our daily database development.

All variables should be in upper case.

For Package,

select text from dba_source where upper(owner)='OBJECTNAME' 
and upper(name)='PACKAGENAME' and upper(type)='PACKAGE' 
and text like '%yourpatten%';

select line,text from dba_source where upper(owner)='OBJECTNAME' 
and upper(name)='PACKAGENAME' and upper(type)='PACKAGE' 
AND LINE=50;

select object_name,object_type,owner,status from dba_objects 
where upper(object_name)='PACKAGENAME';
alter PACKAGE OBJECTNAME compile;
alter PACKAGE OBJECTNAME compile body;
show error package PACKAGENAME;

For Package body

You just need to change the type from ‘PACKAGE’ to ‘PACKAGE BODY’ in above queries.

For Synonyms,

select TABLE_OWNER,TABLE_NAME from dba_synonyms where upper(OWNER)='OBJECTNAME' 
and upper(SYNONYM_NAME)='PACKAGENAME';

For View,

set LONG 100000;
select TEXT from DBA_VIEWS where view_name='VIEWNAME';
alter VIEW VIEWNAME compile;
show error VIEW VIEWNAME;

For Materialized,

alter MATERIALIZED VIEW MATERIALIZEDNAME compile;

Gather the invalid list,

spool invalids.txt
set pages 10000;
column object_name format A30;
column object_type format A25;
column owner format A15;
set pagesize 10000;
select owner,count(*) from dba_objects where upper(status)='INVALID' 
group by owner order by 1;

select object_name,object_type,owner from dba_objects 
where upper(status)='INVALID' order by 1;