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

set echo off
set termout on

-- Check if user is SYS : 

set head off feed off pages 0 termout off echo off verify off 
set timing off
column linie format a130 trunc
spool tlist_indnoextent.tmp
select 'whenever sqlerror exit' linie,
       'prompt Must be run as user SYS' linie,
       'pause Press [RETURN]', 
       'exit' linie
  from dual
 where upper(user) != 'SYS'
/
spool off
set termout on feed on verify on
@tlist_indnoextent.tmp

whenever sqlerror continue

-- Creating table required for collecting index statistics 

set lines 132 echo off termout off
drop table tlist_indnoextent                                           
/
drop view vlist_indnoextent
/
create view vlist_indnoextent as
select kdxstrot+1 height, 
       s.blocks,
       o.name,
       kdxstlrw   lf_rows,
       kdxstlbk   lf_blks,
       kdxstlln   lf_rows_len,
       kdxstlub   lf_blk_len,
       kdxstbrw   br_rows,
       kdxstbbk   br_blks,
       kdxstbln   br_rows_len,
       kdxstbub   br_blk_len,
       kdxstdrw   del_lf_rows,
       kdxstdln   del_lf_rows_len,
       kdxstdis   distinct_keys,
       kdxstmrl   most_repeated_key,
       kdxstlbk*kdxstlub+kdxstbbk*kdxstbub  btree_space,
       kdxstlln+kdxstbln  used_space,
       ceil(((kdxstlln+kdxstbln)*100)/(kdxstlbk*kdxstlub+kdxstbbk*kdxstbub)) pct_used,      
       kdxstlrw/decode(kdxstdis,0,1,kdxstdis)  rows_per_key,
       kdxstrot+1+(kdxstlrw+kdxstdis)/(decode(kdxstdis,0,1,kdxstdis)*2)   blks_gets_per_access
  from sys.obj$ o, sys.ind$ i, sys.seg$ s, sys.x$kdxst
 where kdxstfil = s.file#
   and kdxstblk = s.block#
   and s.file#  = i.file#
   and s.block# = i.block#
   and i.obj#   = o.obj#
/
create table list_noext2$tmp
as select owner,table_name, stat.* 
     from index_stats stat,dba_indexes
    where 1=2
/
create index stats_owner_table_name on 
    list_noext2$tmp(owner,table_name)
/
set termout on

-- generating the required validate index

set pages 0 head off feed off newpage 0 echo off termout off
clear columns
clear breaks
ttitle off
btitle off
clear compute
column linie format a130 wrap
set termout on

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

set termout off
spool tlist_indnoextent.sql

set verify off
select 'prompt Testing: user: '||owner||', table: '||table_name||', index: '||index_name linie,
'validate index '||owner||'.'||index_name||';' linie,
'insert into list_noext2$tmp ' linie,
'select '||''''||owner||''''||','||''''||table_name||''''||', stat.*  ' linie,
' from vlist_indnoextent stat;' linie,
'commit;' linie
from dba_indexes
where upper(owner) not in ('SYS')
and   owner like nvl(upper('&schema_owner')||'%','%')
order by owner,table_name,index_name
/
spool off

-- validate selected indexes:

set termout on
@@tlist_indnoextent.sql

col datum new_value datum noprint
select to_char(sysdate,'dd.mm.yy') datum
from dual
/
set pages 48 lines 132 head on feed on
ttitle left format a8 datum ' / ' sql.user -
center 'indexes with potential risk (no more space in ts)' -
right 'seite: ' format 099 sql.pno -
skip -
center '(alloc. space filled up over 50% and no more space for next extent)' -
skip -
center '(or maxextents reached)' -
skip -
center 'oracle7 version 'format a15 sql.release -
skip 2
col owner format a10 trunc
col tablespace_name format a20 trunc

select i.owner, i.table_name, i.index_name, i.tablespace_name,
       s.pct_used, i.next_extent
  from sys.dba_indexes i, sys.dba_segments seg,   list_noext2$tmp s
 where s.table_name = i.table_name
   and s.name = i.index_name
   and seg.segment_name = i.index_name
   and seg.owner = i.owner
   and upper(seg.segment_type) = 'INDEX'
   and s.owner = i.owner
   and s.pct_used > 50
   and (i.next_extent > (select max(f.bytes)
                           from sys.dba_free_space f
                          where i.tablespace_name = f.tablespace_name)
                             or i.max_extents <= seg.extents)      
order by i.owner, i.table_name, i.index_name
/
ttitle off
clear columns
set pages 24 lines 80
exit