-- #################################################################
--
-- Filename:    list_indnoextent_80.sql
--
-- Version:     Certified for Oracle80
--
-- 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                                           
/
create  or replace view vtlist_indnoextent as
 select kdxstrot+1 height, 
       s.blocks,
       o.name,
       o.subname,
       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 tlist_indnoextent2
as select owner,table_name, stat.* 
    from index_stats stat, dba_indexes
   where 1=2
/
create index stats_owner_table_name on 
   tlist_indnoextent2(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 tlist_indnoextent2 ' linie,
'select '||''''||owner||''''||','||''''||table_name||''''||', stat.*  ' linie,
' from vtlist_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 'page: ' 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 'oracle8 version 'format a15 sql.release -
skip 2
col owner format a10 trunc
col tablespace_name format a20 trunc
spool lsinderr.lis
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,   tlist_indnoextent2 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
/
exit
