List the Number of Extents for all Indexes for a Given Table
-- -- 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 /
Related Scripts
- Segment Space Usage By Owner
- Shows Temporary Tablespace Usage
- Tablespace Space Usage
- View Datafile Info
- Displays Space Usage for Each Datafile
- Show the High Water Mark for a Given Table
- List Tables with More than One Extent
- Show Gaps in Tablespace or Specific Datafile
- Displays Temp Space Currently in use by Users