Script to report used space in database tablespaces

Script to report used space in database tablespaces :

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

Abstract
This script will report disk space used 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 ANY TABLE privilege. Need direct privilege not via a role.

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

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

Description of the Script
This script displays space usage and storage parameters of your tablespaces and datafiles.

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

Sample Output:

Tablespace Datafiles

Name       Located in file_ID + File Name                     Size       Used
---------- -------------------------------------------------- ---------- ----------
EXAMPLE    5 D:\ORADATA\A\EXAMPLE01.DBF                        102,400K    79,232K
SYSAUX     3 D:\ORADATA\A\SYSAUX01.DBF                         286,720K   282,816K
SYSTEM     1 D:\ORADATA\A\SYSTEM01.DBF                         501,760K   493,632K
UNDOTBS1   2 D:\ORADATA\A\UNDOTBS01.DBF                         66,560K     9,920K
USERS      4 D:\ORADATA\A\USERS01.DBF                            5,120K     4,608K

Tablespace definitions

Name           Init     Next %Inc  Min         Max Stat Percent full
---------- -------- -------- ---- ---- ----------- ---- ------------------------------
EXAMPLE      65,536                  1  2147483645 OnL  |*********************       |
SYSAUX       65,536                  1  2147483645 OnL  |*************************** |
SYSTEM       65,536                  1  2147483645 OnL  |*************************** |
UNDOTBS1     65,536                  1  2147483645 OnL  |***                         |
USERS        65,536                  1  2147483645 OnL  |*************************   |

Main Script

SET ECHO off
REM NAME:   TBS_USED_SPACE.SQL
REM --------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT ANY TABLE privilege (directly - not via a role)
REM --------------------------------------------------------------------------
REM AUTHOR:  
REM    Mohammad Abdul Momin Arju
REM    http://arjudba.blogspot.com      
REM ------------------------------------------------------------------------ 
REM PURPOSE:
REM    To display space usage and storage parameters of your tablespaces
REM    and datafiles.
REM ---------------------------------------------------------------------------
REM EXAMPLE:
REM Tablespace Datafiles
REM 
REM Name       Located in file_ID + File Name                     Size       Used
REM ---------- -------------------------------------------------- ---------- ----------
REM EXAMPLE    5 D:\ORADATA\A\EXAMPLE01.DBF                        102,400K    79,232K
REM SYSAUX     3 D:\ORADATA\A\SYSAUX01.DBF                         286,720K   282,816K
REM SYSTEM     1 D:\ORADATA\A\SYSTEM01.DBF                         501,760K   493,632K
REM UNDOTBS1   2 D:\ORADATA\A\UNDOTBS01.DBF                         66,560K     9,920K
REM USERS      4 D:\ORADATA\A\USERS01.DBF                            5,120K     4,608K
REM 
REM Tablespace definitions
REM 
REM Name           Init     Next %Inc  Min         Max Stat Percent full
REM ---------- -------- -------- ---- ---- ----------- ---- ------------------------------
REM EXAMPLE      65,536                  1  2147483645 OnL  |*********************       |
REM SYSAUX       65,536                  1  2147483645 OnL  |*************************** |
REM SYSTEM       65,536                  1  2147483645 OnL  |*************************** |
REM UNDOTBS1     65,536                  1  2147483645 OnL  |***                         |
REM USERS        65,536                  1  2147483645 OnL  |*************************   |
REM
REM --------------------------------------------------------------------------
REM Main text of script follows:

set pause off
set feed off
set verify off
set pagesize 100
set linesize 250
clear screen
col tn   format a10 heading 'Name' trunc
col fn   format a50 heading 'Located in file_ID + File Name'
col bts  format a10 heading 'Size'
col used format a10 heading 'Used'

col ex   format        9999 heading 'NrExt'
col rs   format 999,999,999 heading 'RBSsize'
col init format     999,999 heading 'Init'
col next format     999,999 heading 'Next'
col mi   format         999 heading 'Min'
col ma   format  9999999999 heading 'Max'
col pct  format         990 heading '%Inc'
col st   format          a4 heading 'Stat'
col sn   format         a15 heading 'Segm Name'
col ts   format         a15 heading 'In Table Space'

create or replace view free_view
as
select file_id, sum(bytes) free_bytes
from sys.dba_free_space
group by file_id;

clear screen

prompt Tablespace Datafiles
select d.tablespace_name tn,
       f.file_id||' '||file_name fn,
       to_char(f.bytes/1024,'999,999')||'K' bts,
       to_char( (f.bytes - s.free_bytes)/1024,'999,999')||'K' used
from sys.dba_tablespaces d, sys.dba_data_files f, free_view s
where d.tablespace_name = f.tablespace_name
and   f.file_id = s.file_id(+)
order by d.tablespace_name;

prompt
prompt                  Tablespace definitions

define part1="rpad('|',29*(f.bytes-s.free_bytes)/f.bytes,'*')"
col gr format a30 heading 'Percent full'
select d.tablespace_name tn,
       d.initial_extent init,
       d.next_extent next,
       d.pct_increase pct,
       d.min_extents mi, max_extents ma,
       decode(d.status,'ONLINE','OnL','OFFLINE','OffL') st,
       rpad(&part1,29,' ')||'|' gr
from sys.dba_tablespaces d, sys.dba_data_files f, free_view s
where d.tablespace_name = f.tablespace_name
and   f.file_id = s.file_id
order by d.tablespace_name;
drop view free_view;
set feed on

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