SQL Script Tablespace Fragmentation Script

Updated: 13-Sep-2002
Version: 1.0

Description

This script lists details of the extents within a tablespace. This will help you determine if a tablespace is fragmented or not. 

A tablespace with little or no fragmentation the biggest, smallest and average will be similar (e.g. as in a LOCALLY MANAGED tablespace of UNIFORM SIZE)

Parameters

None

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 


set pages 50

PROMPT
PROMPT Tablespace Freespace Fragmentation Report
PROMPT

column "Blocks" format 999999
column "Free" format 999999
column "Pieces" format 99999
column "Biggest" format 999999
column "Smallest" format 999999
column "Average" format 999999
column "Dead" format 9999
select substr(ts.tablespace_name,1,12) "Tspace",
       tf.blocks "Blocks",
       sum(f.blocks) "Free",
       count(*) "Pieces",
       max(f.blocks) "Biggest",
       min(f.blocks) "Smallest",
       round(avg(f.blocks)) "Average",
       sum(decode(sign(f.blocks-5),-1,f.blocks,0)) "Dead"
from   dba_free_space f,
       dba_data_files tf,
       dba_tablespaces ts
where  ts.tablespace_name=f.tablespace_name
and    ts.tablespace_name = tf.tablespace_name
group by ts.tablespace_name,tf.blocks
/

exit

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links