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

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 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

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