fragx.sql

-- 
-- Title :       fragx.sql 
-- Description : Show details of index fragmentation. 
-- 
-- Usage/Notes : Enter name of index owner. 
--               Enter index name.
-- 
-- Copyright :   ABCdba.com 2014 
-- 
 
SET VERIFY OFF
SET FEEDBACK OFF
 
ACCEPT owner_name PROMPT 'Enter owner name: '
ACCEPT index_name PROMPT 'Enter index name: '
 
COL name HEA 'Index Name' FOR a30
COL del_lf_rows HEAd 'Deleted|Leaf Rows' FOR 99999999
COL lf_rows_used HEA 'Used|Leaf Rows' FOR 99999999
COL ratio HEAd '% Deleted|Leaf Rows' FOR 999.99999
 
ANALYZE INDEX &owner_name..&index_name VALIDATE STRUCTURE
/
 
SELECT
   name, del_lf_rows, lf_rows - del_lf_rows lf_rows_used,
   DECODE(lf_rows,0,0,TO_CHAR( del_lf_rows / (lf_rows)*100,'999.99999' )) ratio
FROM
   index_stats
WHERE
   name = UPPER( '&index_name' )
/
 
SET VERIFY ON
SET FEEDBACK ON

© copyright 2001-2014 ABCdba.com | all rights reserved