Oracle Index Extents Report
This script displays the number of extents for indexes belonging to a specified table or all tables within a schema. It combines data from ALL_TABLES, ALL_INDEXES, and DBA_EXTENTS to show extent count, max extents, row count, and extent sizes, helping DBAs analyze index fragmentation and storage growth.
oraclesqlindexing-statisticsv1.0.0
0 stars0 downloads13 views0 comments
By OracleDba • Created
Code
(39 lines)123456789101112131415161718192021222324252627282930313233343536373839
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/index_extents.sql
-- Author : Tim Hall
-- Description : Displays number of extents for all indexes belonging to the specified table, or all tables.
-- Requirements : Access to the DBA views.
-- Call Syntax : @index_extents (table_name or all) (schema-name)
-- Last Modified: 15/07/2000
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
SELECT i.index_name,
Count(e.segment_name) extents,
i.max_extents,
t.num_rows "ROWS",
Trunc(i.initial_extent/1024) "INITIAL K",
Trunc(i.next_extent/1024) "NEXT K",
t.table_name
FROM all_tables t,
all_indexes i,
dba_extents e
WHERE i.table_name = t.table_name
AND i.owner = t.owner
AND e.segment_name = i.index_name
AND e.owner = i.owner
AND i.table_name = Decode(Upper('&&1'),'ALL',i.table_name,Upper('&&1'))
AND i.owner = Upper('&&2')
GROUP BY t.table_name,
i.index_name,
i.max_extents,
t.num_rows,
i.initial_extent,
i.next_extent
HAVING Count(e.segment_name) > 5
ORDER BY Count(e.segment_name) DESC;
SET PAGESIZE 18
SET VERIFY ON