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;
|| 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