prod_db_diag.sql

REM  This script was written and composed by Brian Diehl on 16 June 1999.

REM  This script examines various V$ parameters.  The script makes suggestions
REM  on mods that can be made to your system if specific conditions exist.  The
REM  report should be run after the system has been up for at least 5 hours
REM  and should be run over a period of time to get a real feel for what the
REM  real condition of the database is.  A one-time sample run on an inactive 
REM  system will not give an accurate picture of what is really occuring within
REM  the database.
REM  The script should be run from an account that has DBA
REM  privileges and on which CATDBSYN.SQL has been run.
    
set pagesize 60 feedback off linesize 80 verify off

column "Name" new_value name2 noprint
column "day1" new_value day noprint

select upper(value) "Name"
     , to_char(sysdate,'YYYY-MON-DD HH:MM:SS') "day1"
from v$parameter 
where name = 'db_name'
;
set termout off
column name2 new_value name3 noprint
select lower('&name2') "name2" from dual;


set termout on
PROMPT ------------------------------------------------------------------------;
PROMPT ---         Database report for &name2 generated &day. ;
PROMPT ------------------------------------------------------------------------;
PROMPT;
PROMPT;
PROMPT ------------------------------------------------------------------------;
PROMPT ---                    TABLE OF CONTENTS;
PROMPT --- 1)  Shared Pool Hit Ratios;
PROMPT ---   A)  Data Dictionary Hit Ratio;
PROMPT ---   B)  Dictionary Cache Contention;
PROMPT ---   C)  Library Cache Miss Ratio;
PROMPT ---   D)  Library Cache Contention;
PROMPT ---   E)  Buffer Hit Ratio Method 1;
PROMPT ---   F)  Buffer Hit Ratio Method 2;
PROMPT --- 2)  System Locks;
PROMPT --- 3)  System Events;
PROMPT --- 4)  Memory High Water Mark;
PROMPT ---   A)  Total Memory for All Sessions;
PROMPT ---   B)  Total Maximum Memory for All Sessions;
PROMPT --- 5)  Sort Area Size Values;
PROMPT ---   A)  Init&name3..ora value;
PROMPT ---   B)  Database Sort Values;
PROMPT --- 6)  SGA Memory Usage for Triggers, Packages and Procedures;
PROMPT --- 7)  Rollback Contention Statistics;
PROMPT ---   A)  By Name;
PROMPT ---   B)  By Segment Type;
PROMPT ---   C)  Percentages;
PROMPT --- 8)  Redo Contention Statistics;
PROMPT --- 9)  Latch Contention;
PROMPT --- 10) SGA Memory Allocations;
PROMPT --- 11) I/O Activity;
PROMPT ---   A)  By Datafile;
PROMPT ---   B)  Total Physical Reads;
PROMPT --- 12) Wait Statistics;
PROMPT --- 13) More Rollback Statistics;
PROMPT ---   A)  Sizes;
PROMPT ---   B)  Usages;
PROMPT --- 14) Tablespace Sizing Information;
PROMPT --- 15) Tablespace Free Space Alerts;
PROMPT --- 16) Free Space Fragmentation Index;
PROMPT --- 17) Fragmented Database Objects;
PROMPT ---   A)  Table Fragmentation;
PROMPT ---   B)  Index Fragmentation;
PROMPT ------------------------------------------------------------------------;

clear columns
column "Data Dict. Gets"         FORMAT 999,999,990
column "Data Dict. cache misses" FORMAT 999,999,990
column "LIBRARY CACHE MISS RATIO"     format 99.9990
column "executions"                   format 999,999,990
column "Cache misses while executing" format 999,999,990
column "reloads" FORMAT 999,999,990
column "logical_reads" format 99,999,999,990
column "phys_reads"    format 999,999,990
column "phys_writes"   format 999,999,990

PROMPT;
PROMPT - 1  ################################################################# ;
PROMPT - This section displays some common shared pool hit ratios.;
PROMPT -;
PROMPT -   1A           =========================;
PROMPT -                == DATA DICT HIT RATIO ==;
PROMPT -                =========================;
PROMPT -  == (Should be higher than 90;
PROMPT -  == else increase the shared_pool_size in init.ora);
PROMPT;

select sum(gets) "Data Dict. Gets",
       sum(getmisses) "Data Dict. cache misses",
       trunc((1-(sum(getmisses)/sum(gets)))*100)
       "Data Dict. Cache Hit Ratio"
from V$ROWCACHE
;

column hitrate format 990.0
PROMPT;
PROMPT -  1B           =========================;
PROMPT - The following measures the level of contention in the;
PROMPT -   dictionary cache for each dictionary object type and;
PROMPT -   display those that have less than an 80% hit rate.;

select substr(parameter, 1, 20) parameter,
       gets,
       getmisses,
       count,
       usage,
       round((1 - getmisses/ decode(gets,0,1,gets)) * 100, 1) hitrate
from v$rowcache
where round((1 - getmisses/ decode(gets,0,1,gets)) * 100, 1) < 80.0
order by 6,1
;

PROMPT;
PROMPT -  1C           ==============================;
PROMPT -               == LIBRARY CACHE MISS RATIO ==;
PROMPT -               ==============================;
PROMPT - == (IF > 1 then increase the shared_pool_size in init.ora);
PROMPT -;
PROMPT - ** NOTE: Increasing SHARED_POOL_SIZE will increase the SGA size.;
PROMPT -;
PROMPT - Library Cache Misses indicate that the Shared Pool is not big;
PROMPT - enough to hold the shared SQL area for all concurrently open cursors.;
PROMPT - If there are never any misses (PINS = 0), you may get a small increase;
PROMPT - in performance by setting CURSOR_SPACE_FOR_TIME = TRUE which prevents;
PROMPT - ORACLE from deallocating a shared SQL area while an application cursor;
PROMPT - associated with it is open.;

select sum(pins) "executions",
       sum(reloads) "Cache misses while executing",
       (((sum(reloads)/sum(pins)))) "LIBRARY CACHE MISS RATIO"
from v$librarycache
;

PROMPT;
PROMPT -   1D           ===========================;
PROMPT -                == LIBRARY CACHE SECTION ==;
PROMPT -                ===========================;
PROMPT -  == (Hit ratio should be > 70, and pin ratio > 70 ...);
PROMPT ;

select namespace,
       trunc(gethitratio * 100) "Hit Ratio %",
       trunc(pinhitratio * 100) "Pin Hit Ratio %",
       reloads "reloads"
from v$librarycache
;

PROMPT;
PROMPT -  1E           *******************************;
PROMPT -               ** BUFFER HIT RATIO METHOD 1 **;
PROMPT -               *******************************;
PROMPT - == (Ratio should be > 70, else increase db_block_buffers in init.ora);

select trunc((1-(sum(decode(name,'physical reads',value,0))/
		(sum(decode(name,'db block gets',value,0))+
		(sum(decode(name,'consistent gets',value,0))))))*100)
       "Buffer Hit Ratio"
from v$sysstat
;

PROMPT;
PROMPT -  1F           *******************************;
PROMPT -               ** BUFFER HIT RATIO METHOD 2 **;
PROMPT -               *******************************;
PROMPT - == (Ratio should be > 70, else increase db_block_buffers in init.ora);

select a.value + b.value "logical_reads",
       c.value           "phys_reads",
       d.value           "phys_writes",
       round(100 * ((a.value+b.value) - c.value) /
             (a.value+b.value))  "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c, v$sysstat d
where a.statistic# = 37
  and b.statistic# = 38
  and c.statistic# = 39
  and d.statistic# = 40
;

col sid		format 999 heading "Sess|ID"
col object_name	format a14 heading "Obj Name or|Trans_ID" trunc
col osuser	format a10 heading "OpSys|User ID"
col username	format a8 heading "Oracle|User ID"
col terminal	format a8 trunc heading "Terminal"
set feedback on

PROMPT;
PROMPT - 2  ################################################################# ;;
PROMPT - System Locks held at time of report;

select	b.sid,
	c.username,
	c.osuser,
	c.terminal,
	decode(b.id2, 0, a.object_name, 'Trans-'||to_char(b.id1)) object_name,
	b.type,
	decode(b.lmode,
		0, '--Waiting--',
		1, 'Null',
		2, 'Row Share',
		3, 'Row Excl',
		4, 'Share',
		5, 'Sha Row Exc',
		6, 'Exclusive',
		   'Other') "Lock Mode",
	decode(b.request,
		0, ' ',
		1, 'Null',
		2, 'Row Share',
		3, 'Row Excl',
		4, 'Share',
		5, 'Sha Row Exc',
		6, 'Exclusive',
		   'Other') "Req Mode"
from	dba_objects	a,
	v$lock		b,
	v$session	c
where	a.object_id (+)		= b.id1
and	b.sid			= c.sid
and	c.username		is not null
order by
	b.sid,
	b.id2
; 

col event format a30
col total_waits format 999,999,990 heading 'Total|Waits'
col total_timeouts format 999,990 heading 'Total|Timeouts'
col time_waited format 99,990 heading 'Time|Waited'
col average_wait format 9990 heading 'Avg|Wait'
col max_wait format 9990 heading 'Max|Wait'
set feedback off

PROMPT;
PROMPT - 3  ################################################################# ;;
PROMPT - The following displays events as of this report that exist;
PROMPT -  in the database.;

select  substr(username,1,8) "Username",
	event,
	sum(total_waits) total_waits,
	sum(total_timeouts) total_timeouts,
	sum(time_waited) time_waited,
	avg(average_wait) average_wait
from v$session_event a, v$session b
where  a.sid = b.sid
  and  username not in ('SYSTEM','SYS')
group by username, event
order by 1,5
;

PROMPT;
PROMPT - 4  ################################################################# ;;
PROMPT -  This section displays the memory high-water mark for;
PROMPT -    all sessions since startup.;

PROMPT -   4A;
select sum(value) || ' bytes' "Total memory for all sessions"
from v$sesstat, v$statname
where name = 'session pga memory'
  and v$sesstat.statistic# = v$statname.statistic#
;

PROMPT -   4B;
select sum(value) || ' bytes' "Total max mem for all sessions"
from v$sesstat, v$statname
where name = 'session pga memory max'
  and v$sesstat.statistic# = v$statname.statistic#
;

column value format 999,999,990
column name format a20
set heading off

PROMPT;
PROMPT - 5  ################################################################# ;;
PROMPT - SORT AREA SIZE VALUES:;
PROMPT -;
PROMPT -  This section displays the in-memory and disk sorts since instance;
PROMPT -                startup.;
PROMPT - To make best use of sort memory, the initial extent of your Users;
PROMPT - sort-work Tablespace should be sufficient to hold at least one sort;
PROMPT - run from memory to reduce dynamic space allocation.  If you are getting;
PROMPT - a high ratio of disk sorts as opposed to memory sorts, setting;
PROMPT - sort_area_retained_size = 0 in init.ora will force the sort area to be;
PROMPT - released immediately after a sort finishes.;

PROMPT -   5A;
select 'INIT&name2..ORA sort_area_size: '||value
  from v$parameter where name like 'sort_area_size'
;
set heading on
PROMPT -   5B;
select name,
       value
from v$sysstat
where name like 'sort%'
;

column executions format 999,999,990
column Mem_used format   999,999,990
column Name format a24

PROMPT;
PROMPT - 6  ################################################################# ;;
PROMPT - This section displays used SGA memory for;
PROMPT -         triggers, packages, and procedures.;

select substr(owner,1,8) Owner,
       substr(type,1,12)  Type,
       substr(name,1,23)  Name,
       executions,
       sharable_mem       Mem_used,
       substr(kept||' ',1,4) "Kept?"
from v$db_object_cache
where type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
order by executions desc
;

col name format a30
col gets format 9,999,999
col waits format 9,999,999
set head on feedback off;
PROMPT;
PROMPT - 7  ################################################################# ;;
PROMPT - ROLLBACK CONTENTION STATISTICS:;
PROMPT -;
PROMPT - GETS - # of gets on the rollback segment header;
PROMPT - WAITS - # of waits for the rollback segment header;

PROMPT -   7A;
select name, waits, gets
from v$rollstat, v$rollname
where v$rollstat.usn = v$rollname.usn
;
set head off
select 'The average of waits/gets is '||
  round((sum(waits) / sum(gets)) * 100,2)||'%'
from v$rollstat
;

PROMPT - If the ratio of waits to gets is more than 1% or 2%, consider ;
PROMPT - creating more rollback segments;
PROMPT -;
PROMPT -   7B;
PROMPT - Another way to gauge rollback contention is:;
PROMPT - data block     = Usually occurs when there are too many modified blocks;
PROMPT -                  in the buffer cache; reduce contention by adding DBWR;
PROMPT -                  processes.;
PROMPT - segment header = May occur when many full table scans execute;
PROMPT -                  simultaneously with data loading process; aggravated;
PROMPT -                  by the parallel options. Reschedule data loading jobs;
PROMPT -                  to reduce contention;
PROMPT - undo blocks    = Very rarely occurs; may be caused by mutliple users;
PROMPT -                  updating records in the same data block at a very;
PROMPT -                  fast rate; can usually resolve the contention by;
PROMPT -                  increasing the pctfree of the tables being modified;
PROMPT -  undo header   = May occur if there are not enough rollback segments;
PROMPT -                  to support the number of concurrent transactions.;


column xn1 format 9999999
column xv1 new_value xxv1 noprint
set head on

select class, count
from v$waitstat
where class in ('system undo header','system undo block','undo header',
  'undo block')
;
set head off verify off
PROMPT -   7C;
select 'Total requests = '||sum(count) xn1, 
       decode(sum(count),0,1,sum(count)) xv1
from v$waitstat
;
select 'Contention for system undo header = '||(round(count/&xxv1,4)) * 100||'%'
from v$waitstat
where class = 'system undo header'
;
select 'Contention for system undo block = '||(round(count/&xxv1,4)) * 100||'%'
from v$waitstat
where class = 'system undo block'
;
select 'Contention for undo header = '||(round(count/&xxv1,4)) * 100||'%'
from v$waitstat
where class = 'undo header'
;
select 'Contention for undo block = '||(round(count/&xxv1,4)) * 100||'%'
from v$waitstat
where class = 'undo block'
;
PROMPT;
PROMPT - If the percentage for an area is more than 1% or 2%, consider ;
PROMPT - creating more rollback segments.  Note:  This value is usually very small;
PROMPT - and has been rounded to 4 places.;
PROMPT;
PROMPT - 8  #################################################################;
PROMPT - REDO CONTENTION STATISTICS:;
PROMPT -;
PROMPT - The following shows how often user processes had to wait for space in ;
PROMPT - the redo log buffer:;

select name||' = '||value
from v$sysstat
where name = 'redo log space requests'
;

PROMPT;
PROMPT - This value should be near 0.  If this value increments consistently,;
PROMPT - processes have had to wait for space in the redo buffer.  If this;
PROMPT - condition exists over time, increase the size of LOG_BUFFER in the;
PROMPT - init.ora file in increments of 5% until the value nears 0.;
PROMPT - ** NOTE: increasing the LOG_BUFFER value will increase total SGA size.;
PROMPT;
col name format a15
col gets format 999999999
col misses format 999999999
col immediate_gets heading 'Immediate|gets' format 999999999
col immediate_misses heading 'Immediate|misses' format 9999999999
col sleeps format 99999999
PROMPT - 9  #################################################################;
PROMPT - LATCH CONTENTION:;
PROMPT -;
PROMPT - GETS - # of successful willing-to-wait requests for a latch;
PROMPT - MISSES - # of times an initial willing-to-wait request was unsuccessful;
PROMPT - IMMEDIATE_GETS - # of successful immediate requests for each latch;
PROMPT - IMMEDIATE_MISSES = # of unsuccessful immediate requests for each latch;
PROMPT - SLEEPS- # of times a process waited and requests a latch after an initial;
PROMPT -          willing-to-wait request;
PROMPT -;
PROMPT - If the latch requested with a willing-to-wait request is not;
PROMPT - available, the requesting process waits a short time and requests again.;
PROMPT - If the latch requested with an immediate request is not available,;
PROMPT - the requesting process does not wait, but continues processing;

set head on;
select name, gets, misses, immediate_gets, immediate_misses, sleeps
from v$latch
where name in ('redo allocation','redo copy')
;
set head off
select 'Ratio of MISSES to GETS: '||round((sum(misses)/sum(gets) * 100),2)||'%'
from v$latch
where name in ('redo allocation','redo copy')
;
select 'Ratio of IMMEDIATE_MISSES to IMMEDIATE_GETS: '||
round((sum(immediate_misses)/
decode(sum(immediate_misses+immediate_gets),0,1,
       sum(immediate_misses+immediate_gets)) * 100),2)||'%'
from v$latch
where name in ('redo allocation','redo copy')
;

PROMPT;
PROMPT - If either ratio exceeds 1%, performance will be affected.;
PROMPT - Decreasing the size of LOG_SMALL_ENTRY_MAX_SIZE reduces the number of;
PROMPT - processes copying information on the redo allocation latch.;
PROMPT - Increasing the size of LOG_SIMULTANEOUS_COPIES will reduce contention for;
PROMPT - redo copy latches.;

set head on
col name format a40
PROMPT;
PROMPT - 10 #################################################################;
PROMPT - THE SGA AREA ALLOCATION:;
PROMPT -  ;
PROMPT - This shows the allocation of SGA storage.  Examine this before and;
PROMPT - after making changes in the INIT.ORA file which will impact the SGA.;

select name, bytes
  from v$sgastat
;

column name format a43 heading 'DataFile Name'
column pbr format 99999999 heading 'Physical|Blk Read'
column pbw format 99999999 heading 'Physical|Blks Wrtn'
column pyr format 999999 heading 'Physical|Reads'
column readtim format 990 heading 'Read|Time'
column writetim format 990 heading 'Write|Time'
compute sum of f.phyblkrd, f.phyblkwrt on report
PROMPT;
PROMPT - 11 #################################################################;
PROMPT - I/O ACTIVITY BY FILE;
PROMPT -;
PROMPT - This looks at overall i/o activity against individual files within a ;
PROMPT - tablespace.  Add up the numbers by disk and look for a mismatch across;
PROMPT - disks in terms of I/O.;
PROMPT - If activity on the files is unbalanced, move files around to balance;
PROMPT - the load.  Should see an approximately even set of numbers across files.;

PROMPT -   11A;
select fs.name name,f.phyblkrd pbr,f.phyblkwrt pbw,f.readtim,
f.writetim
from v$filestat f, v$dbfile fs
where f.file# = fs.file#
order by fs.name
;

PROMPT;
PROMPT -   11B;
set heading off
ttitle off
column value format 999,999,999,999,999,999
select 'Total Physical Reads', value
from v$sysstat
where statistic# = 39
;
PROMPT;
PROMPT - If you can significantly reduce physical reads by adding incremental ;
PROMPT - data buffers...do it.  To determine whether adding data buffers will;
PROMPT - help, set db_block_lru_statistics = TRUE and ;
PROMPT - db_block_lru_extended_statistics = TRUE in the init.ora parameters.;
PROMPT - You can determine how many extra hits you would get from memory as ;
PROMPT - opposed to physical I/O from disk.  **NOTE:  Turning these on will;
PROMPT - impact performance.  One shift of statistics gathering should be enough;
PROMPT - to get the required information.;

set heading on
column class heading 'Class Type'
column count format 99,999,999 heading 'Times Waited'
column time heading 'Total Times' format 99,999,999
PROMPT;
PROMPT - 12 #################################################################;
PROMPT - GENERATING WAIT STATISTICS:;
PROMPT -;
PROMPT - This shows wait stats for the instance.  This may show;
PROMPT - the need for additional rbs, wait lists, db_buffers.;

select class, count, time
from v$waitstat
where count > 0
order by class
;

PROMPT;
PROMPT - Look at the wait statistics generated above (if any). They will;
PROMPT - tell you where there is contention in the system.  There will;
PROMPT - usually be some contention in any system - but if the ratio of;
PROMPT - waits for a particular operation starts to rise, you may need to;
PROMPT - add additional resource, such as more database buffers, log buffers,;
PROMPT - or rollback segments;
PROMPT ;
ttitle off;
column name format a6 heading 'Name'
column rssize format 999,999,999 heading 'Size in|Bytes'
column optsize format 99,999,999 heading 'Optimal|Size'
column hwmsize format 9,999,999,999 heading 'High Water|Mark'
column shrinks format 99,999 heading 'Number of|Shrinks'
column extends format 999,999 heading 'Number of|Extends'
column aveactive format 999,999,999 heading 'Avg size|Act Extents'
column rownum noprint
PROMPT - 13 #################################################################;
PROMPT - ROLLBACK STATISTICS:;

PROMPT -   13A;
select substr(name,1,6) "Name",rssize, optsize, hwmsize, shrinks, 
       extends, aveactive
from v$rollstat, v$rollname
where v$rollstat.usn = v$rollname.usn
order by rownum
;

PROMPT -   13B;
break on report
compute sum of gets waits writes on report
ttitle center 'Rollback Statistics' skip 2
select substr(name,1,6) "Name", extents, rssize, xacts, gets, waits, writes
from v$rollstat, v$rollname
where v$rollstat.usn = v$rollname.usn
order by rownum
;
ttitle off
set heading on
column sbytes format 999,999,999,999 heading 'Total Bytes'
column fbytes format 999,999,999,999 heading 'Free Bytes'
column kount format 9,999 heading 'Free|Chunks'
compute sum of sbytes on report
compute sum of fbytes on report
break on report
PROMPT;
PROMPT - 14 #################################################################;
PROMPT - TABLESPACE SIZING INFORMATION;

select a.tablespace_name, a.bytes sbytes, sum(b.bytes) fbytes, 
   count(*) kount
from dba_data_files a, dba_free_space b
where a.tablespace_name = b.tablespace_name
  and a.file_id = b.file_id
group by a.tablespace_name, a.file_id, a.bytes
order by a.tablespace_name
;

PROMPT;
PROMPT - A large number of Free Chunks indicates that the tablespace may need;
PROMPT - to be defragmented and compressed.;
PROMPT ;

column "%free" format 990.0
column "Tablespace Name" format a20
PROMPT - 15 #################################################################;;
PROMPT - TABLESPACE FREE SPACE;
PROMPT - ;
PROMPT - The following lists all tablespaces with less than 20% free;
PROMPT -   space left.;

select "tn" "Tablespace name",
       sum("bytes") "Bytes",
       sum("used") "# used",
       sum("free") "# free",
       (sum("free")/sum("bytes"))*100 "%free"
from (
      select b.tablespace_name "tn",
             b.bytes "bytes",
             (b.bytes - sum(a.bytes)) "used",
             sum(a.bytes) "free"
      from sys.dba_free_space a, sys.dba_data_files b
      where a.file_id(+) = b.file_id
      group by b.tablespace_name, b.bytes
     )
  group by "tn"
  having (sum("free")/sum("bytes"))*100  < 20.00
  order by (sum("free")/sum("bytes"))*100
;


column fsfi format 999.99 heading "Free space|Fragmentation|index" 
PROMPT;
PROMPT - 16 ################################################################# ;;
PROMPT - FSFI = Free space fragmentation index;
PROMPT - Numbers aproaching 100 are good, 100 is best (i.e. only one extent.);
PROMPT -   Only Tablespaces with FSFI values less than 50 are displayed.;
PROMPT -;
PROMPT - '                     largest extent              1              ';
PROMPT - 'FSFI = 100 * sqrt (------------------ ) * --------------------- ';
PROMPT - '                   sum of all extents   (number of extents)^1/4 ';

select 
      tablespace_name,
      sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) fsfi
from
      dba_free_space
group by tablespace_name
having sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) < 50.0
order by 2
;

PROMPT;
PROMPT - 17 #################################################################;
PROMPT - CHECKING FOR FRAGMENTED DATABASE OBJECTS:;
PROMPT -;
PROMPT - Fragmentation report - If number of extents is approaching Max extents,;
PROMPT - it is time to defragment the table.;
PROMPT - Only Tables/Indexes with extents > MaxExtents/2 are Displayed;
PROMPT ;

set heading on feedback on
clear computes
column segment_name format a30 heading 'Object Name'
column sum(bytes)/1024 format 999,999,990 heading 'Kilo|Bytes Used'
column count(*) format 99990 heading 'No.'
break on owner skip 1
PROMPT -   17A TABLE FRAGMENTATION REPORT;

select substr(a.owner,1,8) "Owner"
     , segment_name
     , substr(segment_type,1,8) "Type"
     , sum(bytes)/1024
     , max_extents
     , count(*)
from dba_extents a, dba_tables b
where segment_name = b.table_name 
  and a.owner = b.owner
having count(*) > (max_extents / 2)
group by a.owner, segment_name, segment_type, bytes, max_extents
order by a.owner, segment_name, segment_type, max_extents
;

PROMPT -   17B INDEX FRAGMENTATION REPORT;

select substr(a.owner,1,8) "Owner"
      , segment_name
      , substr(segment_type,1,8) "Type"
      , sum(bytes)/1024 
      , max_extents
      , count(*) 
from dba_extents a, dba_indexes b
where segment_name = index_name 
having count(*) > (max_extents / 2)
group by a.owner, segment_name, segment_type, max_extents
order by a.owner, segment_name, segment_type, max_extents
;

PROMPT;
PROMPT - ##################################################################;