all_extents.sql

 

---

rem ======================================================================
rem Name:  all_extents.sql
rem 
rem Desc:  Find tables indexes and clusters which have more than a 
rem       specified number of extents.  
rem
rem ======================================================================


rem
rem Display parameters 
rem

set pages 55
set lines 79
set pause off
set echo off
set feed 1
set termout on

rem
rem Capture the current date and time:
rem

set termout off
col currdate new_value now
select to_char(sysdate, 'mm/dd/yy hh24:mi') currdate from dual;
set termout on

rem Title 
rem

ttitle left   "extents.sql" -
       center "Tables, Indexes and Clusters" -
       right  "Page:" sql.pno  skip 1 -
       center "With More than 4 Extents" -
       right  "Date: " now  skip 3 


rem
rem Column formats
rem

col owner form a8
col segment_type form a8 head 'TYPE'
col segment_name form a20 head 'NAME'
col extents form 999 head 'EXTS'
col max_extents form 999 head 'MAX|EXTS'
col initial_extent form 99,999,999 head 'INITIAL|EXTENT'
col next_extent form 99,999,999 head 'NEXT|EXTENT'
col pct_increase form 999 head 'PCT|INCR'


rem
rem Group each user's objects together 
rem

break on owner skip 2 on segment_type

select owner, 
       segment_type,
       segment_name,
       extents,
       max_extents,
       initial_extent,
       next_extent,
       pct_increase
from   sys.dba_segments
where  extents > 0
order by owner, segment_type

spool all_extents.lst
/


rem
rem The title for the report:
rem

ttitle left   "extents.sql" -
       center "Tables and Indexes" -
       right  "Page:" sql.pno  skip 1 -
       center "With a Next Extent Larger Than Corresponding Free Space" -
       right  "Date: " now  skip 3 


rem
rem Set the column formats for the report:
rem

col object_name form a8 head 'OBJECT|NAME'
col object_type form a8 head 'OBJECT|TYPE'
col tablespace_name form a15 head 'TABLESPACE'
col next_extent form 99,999,999 head 'NEXT|EXTENT'

clear breaks

select t.table_name object_name,
       'TABLE' object_type,
       t.tablespace_name,
       t.next_extent
from   sys.dba_tables t
where  t.next_extent >= (select max(bytes)
                            from   sys.dba_free_space fs
                            where  fs.tablespace_name = t.tablespace_name)
union
select i.index_name object_name,
       'INDEX' object_type,
       i.tablespace_name,
       i.next_extent
from   sys.dba_indexes i
where  i.next_extent >= (select max(bytes)
                            from   sys.dba_free_space fs
                            where  fs.tablespace_name = i.tablespace_name)
/

spool off

exit

---