Script to free space and fragmentation in tablespaces

Script to free space and fragmentation in tablespaces :

====================================================================

Abstract
This script will report free space and fragmentation in database tablespaces.

Execution Environment: 
Any client environment which can connect to database and execute SQL. For example, SQL*plus, iSQL*plus.

Required Privileges:
Need SELECT privilege on view DBA_FREE_SPACE and DBA_DATA_FILES.

Usage: 
If you are outside sqlplus then issue,
$ sqlplus username/password @aTBS_FREE_SPACE.SQL
or if you are inside sqlplus then issue,
SQL> @TBS_FREE_SPACE.SQL

Instructions to use:
Copy the script into a file named TBS_FREE_SPACE.SQL and run that script.

Description of the Script
This script displays tablespace free space and fragmentation for each tablespace. It prints the total size, the amount of space available, and a summary of free space fragmentation in that tablespace.

Sample Input:
No input is required as it will report on all tablespaces in the database.

Sample Output:

                     Database Tablespaces Freespace Summary

                   Free     Largest       Total      Available   Pct
   Tablespace     Frags    Frag (KB)       (KB)         (KB)     Used
---------------- -------- ------------ ------------ ------------ ----
USERS                   5          256        5,120          512   90
SYSTEM                  1        8,128      501,760        8,128   98
UNDOTBS1               13       49,088       66,560       54,400   18
EXAMPLE                 3       20,352      102,400       23,168   77
SYSAUX                  1        5,696      286,720        5,696   98
                 --------              ------------ ------------
sum                    23                   962,560       91,904

Script

SET ECHO off 
REM NAME:   TBS_FREE_SPACE.SQL 
REM USAGE:"START TBS_FREE_SPACE.SQL" if you run inside sql*plus.
REM ------------------------------------------------------------------------ 
REM REQUIREMENTS: 
REM  SELECT ON DBA_FREE_SPACE and DBA_DATA_FILES view
REM ------------------------------------------------------------------------ 
REM AUTHOR:  
REM    Mohammad Abdul Momin Arju
REM    http://arjudba.blogspot.com      
REM ------------------------------------------------------------------------ 
REM PURPOSE: 
REM    Displays tablespace free space and fragmentation for each 
REM    tablespace,  Prints the total size, the amount of space available, 
REM    and a summary of freespace fragmentation in that tablespace. 
REM ------------------------------------------------------------------------ 
REM EXAMPLE: 
REM     
REM
REM                     Database Tablespaces Freespace Summary
REM
REM                     Free     Largest       Total      Available   Pct
REM    Tablespace     Frags    Frag (KB)       (KB)         (KB)     Used
REM ---------------- -------- ------------ ------------ ------------ ----
REM USERS                   5          256        5,120          512   90
REM SYSTEM                  1        8,128      501,760        8,128   98
REM UNDOTBS1               13       49,088       66,560       54,400   18
REM EXAMPLE                 3       20,352      102,400       23,168   77
REM SYSAUX                  1        5,696      286,720        5,696   98
REM                  --------              ------------ ------------
REM sum                    23                   962,560       91,904
REM
REM ------------------------------------------------------------------------ 
REM Main text of script: 
 
ttitle - 
   center  'Database Tablespaces Freespace Summary'  skip 2 
 
comp sum of nfrags totsiz avasiz on report 
break on report 
 
col tsname  format         a16 justify c heading 'Tablespace' 
col nfrags  format     999,990 justify c heading 'Free|Frags' 
col mxfrag  format 999,999,990 justify c heading 'Largest|Frag (KB)' 
col totsiz  format 999,999,990 justify c heading 'Total|(KB)' 
col avasiz  format 999,999,990 justify c heading 'Available|(KB)' 
col pctusd  format         990 justify c heading 'Pct|Used' 
 
select 
  total.tablespace_name                       tsname, 
  count(free.bytes)                           nfrags, 
  nvl(max(free.bytes)/1024,0)                 mxfrag, 
  total.bytes/1024                            totsiz, 
  nvl(sum(free.bytes)/1024,0)                 avasiz, 
  (1-nvl(sum(free.bytes),0)/total.bytes)*100  pctusd 
from 
  dba_data_files  total, 
  dba_free_space  free 
where 
  total.tablespace_name = free.tablespace_name(+) 
  and total.file_id=free.file_id(+)
group by 
  total.tablespace_name, 
  total.bytes 
/ 

Following is the output whenever I run this script in my environment.

SQL>  start "E:\Documents\My Blog\Scripts\tbs_free_space.sql"

                     Database Tablespaces Freespace Summary

                   Free     Largest       Total      Available   Pct
   Tablespace     Frags    Frag (KB)       (KB)         (KB)     Used
---------------- -------- ------------ ------------ ------------ ----
USERS                   5          256        5,120          512   90
SYSTEM                  1        8,128      501,760        8,128   98
UNDOTBS1               13       49,088       66,560       54,400   18
EXAMPLE                 3       20,352      102,400       23,168   77
SYSAUX                  1        5,696      286,720        5,696   98
                 --------              ------------ ------------
sum                    23                   962,560       91,904

=============================================================================