Oracle Database

--
-- List the Number of Extents for all Indexes for a Given Table.  (All Tables if 'ALL' is enter for Table_name).
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
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('&&Table_Name'),'ALL',i.table_name,Upper('&&Table_Name'))
AND    i.owner        = Upper('&&Owner')
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
/