List Tables with More than One Extent

--
-- List tables with more than 1 extent.
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
SELECT t.table_name,
       Count(e.segment_name) extents,
       t.max_extents,
       t.num_rows "ROWS",
       Trunc(t.initial_extent/1024) "INITIAL K",
       Trunc(t.next_extent/1024) "NEXT K"
FROM   all_tables t,
       dba_extents e
WHERE  e.segment_name = t.table_name
AND    e.owner        = t.owner
AND    t.owner        = Upper('&Owner')
GROUP BY t.table_name,
         t.max_extents,
         t.num_rows,
         t.initial_extent,
         t.next_extent
HAVING   Count(e.segment_name) > 1
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 the Number of Extents for All Indexes for a Given Table
• Show Gaps in Tablespace or Specific Datafile
• Displays Temp Space Currently in use by Users