Invalid objects in Oracle DB (health check)

Some standard queries to check the state of the Oracle DB

SELECT 'alter '
|| DECODE(LOWER(object_type), 'package body','package',LOWER(object_type))
|| ' ' || owner || '.'
|| object_name
|| DECODE(object_type,'PACKAGE BODY', ' compile body;',' compile;') INVALID_OBJECTS
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID';

SELECT 'ALTER INDEX '|| owner || '.' || index_name || ' rebuild online ;'
FROM dba_indexes WHERE status = 'UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || index_owner || '.' || index_name
|| ' rebuild partition ' || partition_name || ' online ;'
FROM dba_ind_partitions  
WHERE status = 'UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || index_owner || '.' || index_name
|| ' rebuild subpartition ' || subpartition_name || ' online ;'
FROM dba_ind_subpartitions  
WHERE status = 'UNUSABLE';

SELECT name, unrecoverable_change# , unrecoverable_time
FROM v$datafile
ORDER BY 2;
Bookmark and Share

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>