FRAGMENT TABLES , INVALID INDEX, UNUSABLE PARTITION / SUB IDX,

------------------------------------------------------------
-- Details of tables which are fragmented more than 300MB --
------------------------------------------------------------
CLEAR COLUMNS
COLUMN TABLE_NAME FORMAT A33
DEFINE LINE2='                WARNING!!! Details of tables which are fragmented more than 300MB (All sizes are in MB)                 '
TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1 CENTER LINE1

SELECT TABLE_NAME, 
LAST_ANALYZED, 
BLOCKS, 
NUM_ROWS,
ROUND(((BLOCKS*8192/(1024*1024)))) "TOTAL_SIZE", 
ROUND((NUM_ROWS*AVG_ROW_LEN/1024/1024)) "ACTUAL_SIZE", 
ROUND(((BLOCKS*8192/(1024*1024))-(NUM_ROWS*AVG_ROW_LEN/1024/1024))) "FRAGMNTD", 
ROUND((((BLOCKS*8192/(1024*1024))-(NUM_ROWS*AVG_ROW_LEN/1024/1024))/((BLOCKS*8192/(1024*1024))))*100) "% FRAGMNTD"
FROM DBA_TABLES 
WHERE ROUND(((BLOCKS*8192/(1024*1024))-(NUM_ROWS*AVG_ROW_LEN/1024/1024)))>300
AND OWNER = &DB_SCHEMA
ORDER BY 8 DESC;

-------------------------------------------
-- Fragmented indexes those need rebuilt --
-------------------------------------------
CLEAR COLUMNS
DEFINE LINE2='                                    WARNING!!! Fragmented indexes those need rebuilt                                     '
TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1 CENTER LINE1

SELECT OWNER,
INDEX_NAME,
TABLE_NAME,
BLEVEL 
FROM DBA_INDEXES 
WHERE BLEVEL > 3;

SELECT NAME,
HEIGHT,
LF_ROWS,
DEL_LF_ROWS,
(DEL_LF_ROWS/LF_ROWS)*100 AS RATIO 
FROM INDEX_STATS
WHERE (DEL_LF_ROWS/LF_ROWS)*100 > 20;

-----------------------------
-- Invalid objects details --
-----------------------------
CLEAR COLUMNS
COLUMN OWNER FORMAT A10
COLUMN OBJECT_NAME FORMAT A49
COLUMN OBJECT_TYPE FORMAT A19
DEFINE LINE2='                                           WARNING!!! Invalid objects details                                           '
TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1 CENTER LINE1

SELECT OWNER, 
OBJECT_NAME, 
OBJECT_TYPE, 
CREATED, 
LAST_DDL_TIME
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
AND OWNER = &DB_SCHEMA
ORDER BY 1, 5;

---------------------------
-- Invalid index details --
---------------------------
CLEAR COLUMNS
DEFINE LINE2='                                            WARNING!!! Invalid index details                                            '
TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1 CENTER LINE1

SELECT INDEX_NAME,
INDEX_TYPE,
UNIQUENESS, 
PARTITIONED, 
VISIBILITY
FROM DBA_INDEXES
WHERE OWNER = &DB_SCHEMA
AND STATUS = 'INVALID'
ORDER BY 1;

----------------------------------------
-- Unusable partitioned index details --
----------------------------------------
CLEAR COLUMNS
DEFINE LINE2='                                     WARNING!!! Unusable partitioned index details                                      '
TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1 CENTER LINE1

SELECT INDEX_NAME,
PARTITION_NAME,
STATUS 
FROM DBA_IND_PARTITIONS
WHERE INDEX_OWNER = &DB_SCHEMA
AND STATUS = 'UNUSABLE'
ORDER BY 1, 2;

-------------------------------------------
-- Unusable subpartitioned index details --
-------------------------------------------
CLEAR COLUMNS
DEFINE LINE2='                                    WARNING!!! Unusable subpartitioned index details                                    '
TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1 CENTER LINE1

SELECT INDEX_NAME,
PARTITION_NAME,
STATUS 
FROM DBA_IND_SUBPARTITIONS
WHERE INDEX_OWNER = &DB_SCHEMA
AND STATUS = 'UNUSABLE'
ORDER BY 1, 2;