SQL Script Database Tablespace Usage

Updated: 28-Sep-2002
Version: 2.1

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

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

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 desc) a
where rownum < 11
/

Related Links

Oracle 8: (SQL Script) Database Tablespace Usage

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