/* ************************************************ */
/*                                                  */
/* format the report:                               */
/*        Extent and Tablespace Report              */
/*                                                  */
/* name:    @c:\oracle\sql\extent_2.sql             */
/* date:    12-MAY-95                               */
/* creator: Rick Kupcunas (Oracle)                  */
/*                                                  */
/* ************************************************ */

clear col
set head off
set pause off
set pages 0
set lines 132
set verify off
set feedback off
set termout off

truncate table table_rowids 
/

insert into table_rowids
(owner,segment_name,segment_type,extents,bytes,blocks,actual_blocks)
select owner,segment_name,segment_type,extents,bytes,blocks,0
from sys.dba_segments
where segment_type = 'TABLE'
and bytes > 1024546
/
spool extent_2.rslt

select 'UPDATE TABLE_ROWIDS ' nl, 'SET actual_blocks= ( select count(distinct substr(rowid,1,8)||substr(rowid,15)) from '||owner||'.'||segment_name||') ' nl,'WHERE OWNER= '''||owner||''' AND SEGMENT_NAME = '''||segment_name||''' ; ' from table_rowids
/
spool off

@@/tmp/extent_status.lst

commit;
clear breaks
clear compute
set pages 2000 
spool extent_status.rpt

ttitle center 'Extent and Tablespace Report' - skip 1 

set head on
set lines 80
set verify on
set feedback on
col table_name format a30
col ext format 990
col used format 99,990
col empty format 99,990
col used_pct format 990.99
col next format 99,999,990
Prompt 
Prompt *****************************************************************************
Prompt TEST #1
Prompt
Prompt Checking for Next Extents that will not fit in the current 
Prompt Tablespace
Prompt *****************************************************************************
col available_space heading Available format 999,999,990
col tablespace_name heading TableSp. format a10
col segname heading 'Name'  format a30
col segtype heading 'Type' format a10
col max_nex heading 'Next' format 999,999,990
select b.segment_name segname,b.segment_type segtype,a.tablespace_name tablespace_name,max(a.bytes) available_space, max(b.next_extent) max_nex
from sys.dba_free_space a,sys.dba_segments b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name,b.segment_name,b.segment_type
having max(b.next_extent) > max(a.bytes)
order by 2 desc
/
Prompt
Prompt *****************************************************************************
Prompt TEST #2
Prompt
Prompt Checking For Tables with PCTINCREASE > 0
Prompt *****************************************************************************             
col owner heading "Owner" format a10
col table_name heading "Table Name " format a30
col pct_increase heading "PctIncrease" format 990.90
col Next_extent heading "Next Extent" format 999,999,990
col extents heading "Extents" format 990
select b.owner,b.table_name,b.pct_increase,a.extents,decode(sign(a.extents-2),-1,a.next_extent,CEIL(a.next_extent*((100+b.pct_increase)/100))) Next_extent  
 from dba_segments a,dba_tables b
where 
 a.owner = b.owner
 and a.segment_name = b.table_name
 and b.pct_increase > 0
 and b.pct_increase != 100
order by 1
/
clear breaks
clear compute
col segment_name format a30
col extents format 990 

set head on
set verify on
set feedback on
set termout on
col owner heading "Owner     " format a10
col blocks heading Blk format 999,990
col segment_name heading "Segment Name                 " format a30
col extents heading Ext format 990
col act_blocks heading "A.Blk" format 999,990
col pct heading Pctg format 990.90
col segment_type heading "SegmentType"format a10

Prompt
Prompt *****************************************************************************
Prompt TEST #3
Prompt
Prompt Checking Data Block Growth ( 90% or higher )...
Prompt Note:
Prompt A.Blk = Actual Data Blocks
Prompt Blk   = Allocated Data Blocks
Prompt
Prompt *****************************************************************************
select a.owner owner,a.segment_name segment_name,a.segment_type segment_type,a.blocks blocks,decode(a.actual_blocks,0,1,a.actual_blocks) act_blocks,decode(a.actual_blocks,0,1,a.actual_blocks)/a.blocks*100 pct
from table_rowids a,dba_tables b
where  a.owner = b.owner 
and a.segment_name = b.table_name
and decode(a.actual_blocks,0,1,a.actual_blocks)/a.blocks*100 > 90
/
col segment_type heading "SegType" format a15
col action format a10
col owner format a10
col extents heading "Ext" format 990
col max_extents heading "Max" format 990
col table_name heading "Table Name" format a30
Prompt 
Prompt *****************************************************************************
Prompt TEST #4
Prompt
Prompt Segments With More Than Twenty Extents...
Prompt Criteria:
Prompt 20 - 50  Extents --> Warning
Prompt      51+ Extents --> Recreate
Prompt (Extents/MaxExtents > 50%) --> Recreate  
Prompt 
Prompt *****************************************************************************
Prompt
select owner,segment_name  table_name,segment_type,extents,max_extents ,decode(sign(extents-50),1,'*RECREATE*','*WARNING*') Action
from dba_segments
where
extents >20   
and segment_type in ('TABLE','INDEX','CLUSTER')
UNION
select owner,segment_name  table_name,segment_type,extents,max_extents ,decode(sign(extents-
50),1,'*RECREATE*','*WARNING*') Action
from dba_segments
where
(extents/max_extents*100) > 50
and segment_type in ('TABLE','INDEX','CLUSTER')
order by 4 desc 
/
set feedback off
set termout off
set verify off
delete table_utilization
/
insert into table_utilization
    (tablespace_name,total_bytes)
    select tablespace_name,sum(bytes)
    from dba_data_files
    group by tablespace_name
/ 
update table_utilization b
set free_bytes = ( select sum(a.bytes) from dba_free_space a
                   where a.tablespace_name = b.tablespace_name
                  ),
    fragments =  ( select count(*) from dba_free_space c
                   where c.tablespace_name = b.tablespace_name
                  )
/
commit
/
set feedback on
set verify on
set termout on
Prompt *****************************************************************************
Prompt TEST #5
Prompt 
Prompt Tablespace Utilization
Prompt
Prompt *****************************************************************************
col tablespace_name heading "TableSpace|Name" format a30
col total_bytes heading "Size|(Kbytes)" format 999,999,990
col free_bytes heading "Free|(KBytes)" format 999,999,990
col pct heading "%|Util" format 990.990
col fragments heading "Fragments" format 9990
select tablespace_name,total_bytes/1024 total_bytes,free_bytes/1024 free_bytes ,(total_bytes-free_bytes)/total_bytes * 100 PCT,fragments
from table_utilization
order by 4 desc
/
set verify off
set feedback off
Prompt *****************************************************************************
Prompt TEST #6
Prompt
prompt Calculation of Dictionary Cache Hit Ratio
Prompt
Prompt *****************************************************************************
column count  format 999,990 heading Count
column usage format 99,990 heading Usage  
column gets  format 99,999,990 heading Gets
column getmisses format 99,999,990 heading Misses
column a format 990.99 heading "Hit Ratio"
select substr(parameter,1,25) parameter,count,usage,decode(count-usage,0,'*',' ') Rec,gets,getmisses,((1-(decode(gets,0,0,getmisses))/decode(gets,0,1,gets))*100) a
from v$rowcache
order by 6 desc
/
Prompt
Prompt *****************************************************************************
Prompt TEST #7
prompt
prompt Calculating Table/Index Access Ratio 
Prompt *****************************************************************************
column avalue format 999,999,999,990 heading "Table Hits"
column bvalue format 999,999,999,990 heading "Index Hits"
column ht format 990.99 heading "Table %"
column it format 990.99 heading "Index %"
col acr heading "Access"
select a.value avalue,b.value bvalue,(1-b.value/(a.value+b.value))*100 ht,(1-a.value/(a.value+b.value))*100 it,decode ( sign(75 - CEIL( (1-a.value/(a.value+b.value))*100)),1,'*BAD ACCESS RATIO*',0,'*AVG ACCESS RATIO*','*GOOD ACCESS RATIO*') ACR
from v$sysstat a,v$sysstat b
where a.statistic# = 109
and b.statistic# = 111;
prompt
set head off
Prompt
Prompt *****************************************************************************
Prompt TEST #8
Prompt
prompt Sort Activity
Prompt *****************************************************************************
column value format 999,999,990
select 'Sort Area Size : '||value
from v$parameter
where name like '%sort_area_size%';
set head on
column avalue format 999,999,990 heading Sorts(Memory)
column bvalue format 999,999,990 heading Sorts(Disks)
column ht format 990.99 heading "Memory%"
column it format 990.99 heading "Index%"
select a.value avalue,b.value bvalue,(1-b.value/(a.value+b.value))*100 ht,
(1-a.value/(a.value+b.value))*100 it
from v$sysstat a,v$sysstat b
where a.statistic# =  119
and b.statistic# = 120
/
Prompt
Prompt *****************************************************************************
Prompt TEST #9
prompt
prompt Calculating Buffer Busy Waits
prompt 
Prompt *****************************************************************************
col count format 999,999,990
select class,count
from v$waitstat
where class in (
'free list','system undo header','system undo block','undo header','undo block')
/
spool off
clear col
clear breaks
clear compute
set head on
set pause off
commit;

