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;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.