Wednesday, December 18, 2019

How to generate a report of all invalid objects in the database? (Oracle)

When should we use this script?
--Before and after upgrading your database
--Before and after deploying new DDL changes to your database
--Debug ORA-04063 or similar errors


DOC
#######################################################################
#######################################################################
   The following statement will generate a html report Invalid.html
   which shows invalid object details of the database.
   Please note that the last two statements might fail in higher db
   versions.
#######################################################################
#######################################################################
#

spool Invalid.html

set pagesize 50000
set markup html on

col comp_id format a10
col comp_name format a30
col version format a10
col object_name format a30
col owner format a15
select * from v$version;
select comp_id, comp_name, status, version from dba_registry;
select count(*) from dba_objects where status='INVALID';
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;
select owner,object_name,object_type, status from dba_objects where status = 'INVALID';
select owner,object_name,object_type, status from dba_objects where status = 'INVALID'and owner IN ('SYSTEM' , 'SYS' );
select comp_name, version,status from dba_registry where STATUS <> 'VALID';
select * from registry$sys_inv_objs;
select * from registry$nonsys_inv_objs;

set markup html off
spool off

No comments:

Post a Comment

My own Mind Map program in Java script and Python

I had been searching online mindmap apps for my study for a while and never got one that I am really happy with.  Then I asked myself what I...