set termout off
store set sqlplus_settings replace

set serveroutput on size 1000000
set linesize 120
set pagesize 120

set verify off
set head off
ttitle off
btitle off

clear columns
clear breaks

PROMPT
PROMPT ###################################################################### 
PROMPT 
PROMPT --  Name          : listInvalidObjectsInfo.sql
PROMPT     
PROMPT --  Copyright (c) DTA Inc., All Rights Reserved (2003)
PROMPT 
PROMPT --  Description   :This script lists all objects that are currently invalid.
PROMPT 
PROMPT --  Input(s)     : None
PROMPT 
PROMPT --  Output       : The output is in a file named invalidObjectsInfo_<DBNAME>_<HOST>.lst
PROMPT 
PROMPT --  Note         : Run this script while connected as the SYS user
PROMPT ######################################################################
PROMPT 

column  owner       format a18   heading "Owner"        wrap
column object_type  format a15   heading "Object|Type"  wrap
column object_name  format a30   heading "Object|Name"  wrap
column num_objects  format 99999 heading "Number|Invalid|Objects"
column status       format a7

-- Get database name and store in variable
column name new_value s_dbname
SELECT rtrim(name) name FROM v$database;

-- Get today's date
column today new_value s_curDate
SELECT to_char(sysdate, 'Month DD, YYYY') today FROM dual;

-- Get host name and store in variable
column host_name new_value s_machine
SELECT host_name from v$instance;

set termout on
set feedback off

set head on

spool invalidObjectsInfo_&s_dbname._&s_machine

ttitle left &s_curDate -
       center "Invalid Object SUMMARY By Owner for "  &s_dbname " on " &s_machine skip 2

break on owner skip 2
compute sum label "Total" of num_objects on owner

select owner,
       object_type,
       count(*) num_objects
from dba_objects
where status <> 'VALID'
group by owner, object_type;
 
ttitle left &s_curDate -
       center "Invalid Object LISTING By Owner for "  &s_dbname " on " &s_machine skip 2

select owner,
       object_type,
       object_name, status 
from dba_objects
where status = 'INVALID'
order by owner, object_type, object_name;
 
spool off

clear computes

ttitle off
btitle off
clear columns
clear breaks
set feedback on
set termout on
set verify on 

PROMPT
PROMPT -- If file is empty no invalid object information was found for the database &s_dbname
PROMPT
PROMPT
PROMPT Output file invalidObjectsInfo_&s_dbname._&s_machine..LST produced
PROMPT

@sqlplus_settings