This old script might work:
set verify off
set pagesize 35
set linesize 132
set pause on
set pause 'Hit enter to continue'
set feedback off
set showmode off
set echo off
set space 0
set heading off
set termout off pause off
column blsk new_value BLOCK_SIZE_K
select value / 1024 blsk
from v$parameter
where name = 'db_block_size';
set termout on pause off
PROMPT
ACCEPT USER_INPUT1 CHAR PROMPT 'Please enter a index to analyze:>'
ACCEPT USER_INPUT2 CHAR PROMPT 'Please enter a owner to analyze:>'
PROMPT
PROMPT Working, Please wait.....
PROMPT
analyze index &USER_INPUT2..&USER_INPUT1 validate structure;
col ROWS_PER_KEY format 999.99 heading ' '
col BLKS_GETS_PER_ACCESS format 99,999.99 heading ' '
select
'**************************************************************************',
' Name of the index = ' || NAME,
' Height of the b-tree = ' || HEIGHT,
' Blocks allocated to the index = ' || BLOCKS,
' Number of leaf rows (values in the index) = ' || LF_ROWS,
' Number of leaf blocks in the b-tree = ' || LF_BLKS,
' Sum of the lengths of all the leaf rows = ' || LF_ROWS_LEN,
' Useable space in a leaf block = ' || LF_BLK_LEN,
' Number of branch rows = ' || BR_ROWS,
' Number of branch blocks in the b-tree = ' || BR_BLKS,
'Sum of lengths of all the branch blocks in the b-tree = ' || BR_ROWS_LEN,
' Useable space in a branch block = ' || BR_BLK_LEN,
' Number of deleted leaf rows in the index = ' || DEL_LF_ROWS,
' Total length of all deleted rows in the index = ' || DEL_LF_ROWS_LEN,
' Number of distinct keys in the index = ' || DISTINCT_KEYS,
' How many times the most repeated key is repeated = ' || MOST_REPEATED_KEY,
' Total space currently allocated in the b-tree = ' || BTREE_SPACE,
'Totl space that is currently being used in the b-tree = ' || USED_SPACE,
'% of space allocated in the b-tree that is being used = ' || PCT_USED,
' Average number of rows per distinct key = ' || ROWS_PER_KEY,
'Expected number of consistent mode block gets per row = ' || BLKS_GETS_PER_ACCESS
from index_stats;
col NAME format a30 fold_after
col dummy_col_0 fold_after
col dummy_col_1 fold_after
col dummy_col_2 fold_after
col LF_ROWS format 9,999,990 heading ' '
col DEL_LF_ROWS format 9,999,990 heading ' '
col BR_ROWS format 9,999,990 heading ' '
col DISTINCT_KEYS format 9,999,990 heading ' '
col MOST_REPEATED_KEY format 9,999,990 heading ' '
col ROWS_PER_KEY format 9,999,990 heading ' '
col HEIGHT format 0 fold_after heading ' '
col BLKS_GETS_PER_ACCESS format 99,999.99 fold_after heading ' '
col LF_BLK_LEN format 9,990 fold_after heading ' '
col lf_row_size format 990 fold_after heading ' '
col lf_row_per_blk format 990 fold_after heading ' '
col BR_BLK_LEN format 9,990 fold_after heading ' '
col br_row_size format 990 fold_after heading ' '
col br_row_per_blk format 990 fold_after heading ' '
col meg format 999.90 fold_after heading ' '
col lf_meg format 999.90 heading ' '
col lf_meg_pct format 99.90 fold_after heading ' '
col br_meg format 999.90 heading ' '
col br_meg_pct format 99.90 fold_after heading ' '
col uu_meg format 999.90 heading ' '
col uu_meg_pct format 99.90 fold_after heading ' '
col bt_meg format 999.90 heading ' '
col bt_meg_pct format 99.90 fold_after heading ' '
col btu_meg format 999.90 heading ' '
col btu_meg_pct format 99.90 fold_after heading ' '
col btuu_meg format 999.90 heading ' '
col btuu_meg_pct format 99.90 fold_after heading ' '
col btuub_meg format 999.90 heading ' '
col btuub_meg_pct format 99.90 fold_after heading ' '
set pause on
select
'************************************************************************' dummy_col_0,
'Index Name.........', NAME, ' ' dummy_col_1,
'Leaf Rows..........', LF_ROWS, ' ',
'Leaf Block Size........', LF_BLK_LEN,
'Deleted Leaf Rows..', DEL_LF_ROWS, ' ',
'Leaf Row Size..........', LF_ROWS_LEN / LF_ROWS lf_row_size,
'Branch Rows........', BR_ROWS, ' ',
'Leaf Rows Per Block....', LF_BLK_LEN / (LF_ROWS_LEN / LF_ROWS) lf_row_per_blk,
'Distinct Keys......', DISTINCT_KEYS, ' ',
'Branch Block Size......', BR_BLK_LEN,
'Max Common Key.....', MOST_REPEATED_KEY, ' ',
'Branch Row Size........', BR_ROWS_LEN / (BR_ROWS + 1) br_row_size,
'Avg Common Key.....', ROWS_PER_KEY, ' ',
'Branch Rows Per Block..', BR_BLK_LEN / ((BR_ROWS_LEN / (BR_ROWS + 1)) + 1) br_row_per_blk,
'Height Of B-Tree...', HEIGHT,
'Reads Per Access...', BLKS_GETS_PER_ACCESS,
' ' dummy_col_2,
'Index Meg................', (BLOCKS * &BLOCK_SIZE_K) / 1024 meg,
'Leaf Meg/Pct.............', (LF_BLKS * &BLOCK_SIZE_K) / 1024 lf_meg, ' /',
(LF_BLKS / BLOCKS) * 100 lf_meg_pct,
'Branch Meg/Pct...........', (BR_BLKS * &BLOCK_SIZE_K) / 1024 br_meg, ' /',
(BR_BLKS / BLOCKS) * 100 br_meg_pct,
'Unused Meg/Pct...........', ((BLOCKS - (LF_BLKS + BR_BLKS)) * &BLOCK_SIZE_K) / 1024 uu_meg, ' /',
((BLOCKS - (LF_BLKS + BR_BLKS)) / BLOCKS) * 100 uu_meg_pct,
'B-Tree Meg/Pct...........', ((LF_BLKS + BR_BLKS) * &BLOCK_SIZE_K) / 1024 bt_meg, ' /',
((LF_BLKS + BR_BLKS) / BLOCKS) * 100 bt_meg_pct,
'B-Tree Used Meg/Pct......', (USED_SPACE / 1024) / 1024 btu_meg, ' /',
PCT_USED btu_meg_pct,
'B-Tree UnUsed Meg/Pct....', ((BTREE_SPACE - USED_SPACE) / 1024) / 1024 btuu_meg, ' /',
100 - PCT_USED btuu_meg_pct,
'B-Tree UnUsable Meg/Pct..', (DEL_LF_ROWS_LEN / 1024) / 1024 btuub_meg, ' /',
(((DEL_LF_ROWS_LEN / 1024) / 1024) /
(((LF_BLKS + BR_BLKS) * &BLOCK_SIZE_K) / 1024)) * 100 btuub_meg_pct
from index_stats;
-----Original Message-----
From: Seema Singh [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 29, 2001 3:06 PM
To: Multiple recipients of list ORACLE-L
Subject: Index fragmentation
Hi Gurus
How I can know about Index fragmentation?
Thanks in advance for reply
-Seema
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seema Singh
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
