extents2.sql

/* list table name and number of extents where extents is greater than 1 */

select substr(owner,1,12) "OWNER", 
       substr(segment_name,1,20) "OBJECT NAME",
       segment_type "TYPE",
        extents
from sys.dba_segments
where segment_type = 'TABLE' and 
      extents > 1 and
      owner not in ('SYS','SYSTEM');



/* list index names and number of extents where extents is greater than 1 */

select substr(owner,1,12) "OWNER", 
       substr(segment_name,1,20) "OBJECT NAME",
       segment_type "TYPE",
        extents
from sys.dba_segments
where segment_type = 'INDEX' and 
      extents > 1 and
      owner = 'SYSADM';

/* list rollback segement names and number of extents where extents is greater than 1 */

select substr(owner,1,12) "OWNER", 
       substr(segment_name,1,20) "OBJECT NAME",
       segment_type "TYPE",
        extents
from sys.dba_segments
where segment_type = 'ROLLBACK';