rem Script: dbb_index_stats.sql
rem Used by dbbackup.shl to create the index statistics for PROD.
set showmode off
set echo off
set heading off
set pagesize 0
set timing off
set feedback off
rem accept indexname char prompt 'Enter index name (or pattern) for statistics: '
rem accept delonly char prompt 'Only indexes with deleted rows (Y or N)? '
define indexname = %
define delonly = Y
set termout off
set linesize 80
set verify off
spool index_statsa.sql
select 'define indexname = ' || upper('&indexname') from dual;
spool off
@index_statsa.sql
spool index_statsa.sql
select 'define delrows = ' || decode('&delonly','Y',1,'y',1,0) from dual;
spool off
@index_statsa.sql
set space 0
col "Name" format a20 trunc
col "Tot Row" format 9999999
col "Tot Len" format 99999999
col "Del Row" format 9999999
col "Del Len" format 9999999
col "Del %" format 999.99
col "Ext" format 999
col " Tablspac" format a9 trunc
spool index_statsa.sql
select 'validate index ' || owner || '.' || index_name || ';' || chr(10) ||
'select rpad(name,19) "Name",''' || rpad(owner,7) || ''',lf_rows "Tot Row",' || chr(10) ||
'lf_rows_len "Tot Len",del_lf_rows "Del Row",' || chr(10) ||
'del_lf_rows_len "Del Len",decode(lf_rows_len,0,' || chr(10) ||
'decode(del_lf_rows_len,0,0.0,100.0),' || chr(10) ||
'decode(sign(del_lf_rows_len/lf_rows_len*100-100.0),1,100.0,' || chr(10) ||
'del_lf_rows_len/lf_rows_len*100)) "Del %",extents "Ext",' || chr(10) ||
''' '' || tablespace_name " Tablspac"' || chr(10) ||
'from index_stats,dba_segments where name = segment_name' || chr(10) ||
'and name = ''' || index_name || ''' and del_lf_rows >= &delrows' || chr(10) ||
'and owner = ''' || owner || ''';'
from dba_indexes where index_name like '&indexname'
and owner not in ('SYS','SYSTEM','SCOTT');
spool off
spool index_stats.lst
@index_statsa.sql
spool off
!sort -r index_stats.lst | sort +5n -r >index_statsa.lst
!echo `date` >index_stats.lst
!echo >>index_stats.lst
!echo 'Name                Owner   Tot Row  Tot Len Del Row Del Len  Del % Ext Tablspac' >>index_stats.lst
!echo '------------------- ------- ------- -------- ------- ------- ------ --- --------' >>index_stats.lst
!cat index_statsa.lst >>index_stats.lst
!rm index_statsa.sql
!rm index_statsa.lst
!/home/common/all_rights.shl index_stats.lst
rem !/home/common/view_or_print.shl index_stats.lst 'Index Statistics'
set space 1
set linesize 80
set termout on
set heading on
set pagesize 24
set timing on
set feedback 6
set verify on
set echo on
set showmode both
exit