SQL Script Database Tablespace Usage

Updated : 28-July-2002
Version : 2.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.

This script now limits to just the top 10.

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 rownum as rank, a.*
from (
   select owner, tablespace_name, sum(bytes)/1024/1024 Mb
   from dba_segments
   where owner not in ('SYS','SYSTEM')
   group by owner,tablespace_name
   order by 3 desc ) a
where rownum < 11
/

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 rownum as rank, a.*
from (
   select tablespace_name, owner, sum(bytes)/1024/1024 Mb
   from dba_segments
   where owner not in ('SYS','SYSTEM')
   group by tablespace_name, owner
   order by 3) a
where rownum < 11
/

Previous Oracle Version Links

Database Tablespace Usage

Return to Index of SQL Scripts

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