-- #################################################################
--
-- Filename:    list_tabnoextent_73.sql
--
-- Version:     Certified for Oracle73
--
-- Purpose:     Show tables which cannot allocate a next extent
--
-- Environment: This Script must be executed by the SYS owner.
--
-- #################################################################

ACCEPT schema_owner CHAR PROMPT 'Schema-Owner or Wildcard <%> : ' DEFAULT '%'
ACCEPT schema_table CHAR PROMPT 'Schema-Table or Wildcard <%> : ' DEFAULT '%'

set feed on
set verify off
set pagesize 10000
set linesize 200
set wrap off
set scan on
set termout on

ttitle left 'Tables unable to allocate a next extent' skip 2

column datum            new_value     datum    noprint
column extents          format        999      heading EXT
column used_blocks      format        9999999  heading USED_BLOCKS
column allo_blocks      format        9999999  heading ALLOC_BLOCKS
column space_for_recs   format        9999999    
column next_extent      format        9999999  heading NEXT_EXT

select to_char(sysdate,'dd.mm.yyyy') datum,
      rpad(t.owner,10,'.') owner,
      rpad(t.table_name,30,'.') table_name,
      rpad(t.tablespace_name,30,'.') tablespace_name,
      extents,
      t.blocks used_blocks,
      s.blocks allo_blocks,
         (((value-57-4-(ini_trans*24))*empty_blocks)*
         (1-pct_free/100))/avg_row_len space_for_recs,
      s.next_extent
 from sys.dba_tables t, sys.dba_segments s, v$parameter
where t.owner = s.owner
  and t.table_name = s.segment_name
  and t.owner like upper('&schema_owner')
  and t.table_name like upper('&schema_table')
  and name = 'db_block_size'
  and avg_row_len > 0
  and num_rows > 0
  and 100/s.blocks*t.blocks >= 50
  and (s.next_extent > (select max(f.bytes)
                          from sys.dba_free_space f
                         where f.tablespace_name = t.tablespace_name)
                            or t.max_extents <= s.extents) 
order by t.tablespace_name, t.owner, t.table_name
/
exit
                           
           
