SQL Script Database Tablespace Usage

Updated : 28-Feb-2001
Version : 1.0

Description

This script lists gives a breakdown of who is using the space in each tablespace. It groups firstly by tablespace and then a second query groups by owner first. It should help in determining a tablespace quota policy.

Parameters

None.

SQL Source

set pages 200

col Mb form 9,999,9999

break on report on owner skip 1
compute sum of Mb on report 
compute sum of Mb on owner 

select owner, tablespace_name, sum(bytes)/1024/1024 Mb
from dba_segments
where owner not in ('SYS','SYSTEM')
group by owner,tablespace_name
/

clear breaks
clear computes

break on report on tablespace_name skip 1

compute sum of Mb on report 
compute sum of Mb on tablespace_name

select tablespace_name, owner, sum(bytes)/1024/1024 Mb
from dba_segments
where owner not in ('SYS','SYSTEM')
group by tablespace_name, owner
/

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal