This section describes how to check for fragmentation in an Oracle database. For more information, see Managing Tables and Indexes (Oracle).
You can check for fragmentation using BR*Tools or the database features supplied by Oracle:
· Check for unused space in a table using BRSPACE.
You can do this interactively or using command line options, as follows:
Ў Interactively, start BRSPACE with:
brspace –f dbshow –c tbinfo –t <table_name>
For more information, see Command options for BRSPACE.
Ў Using command line options to get a report including index statistics, for example:
brconnect –u / -c –f stats –t all_ind –m +I
For more information, see Command Options for BRCONNECT.
Towards the end, the report contains index statistics (charts of 20 empty indexes, using validate structure). The values of the two columns used_by_btree and used are important. The percentage used of used_by_btree is equivalent to the pct_used described below. It should not be below 50%.
· Check for unused space in an index using Oracle database features.
The steps are as follows:
a. Determine the index you want to analyze.
b. Log in to SQL*Plus as user SAPR3.
c. Run the command analyze index <NAME> validate structure
d. Display statistics with the command select * from index_stats
The column pct_used returns the average used space in index blocks. If this is below 50% the index is considered to have excessive unused space.