table space usage script required.

Looking for a SQL script that will report back on the objects in a particular tablespace and their respective size and percentage of the total tablespace size.

Reason for this is we have a number of table spaces that we are having to add datafiles to on a daily basis but we dont have visibility of what is actually using the space.
Who is Participating?
Naveen KumarProduction Manager / Application Support ManagerCommented:
this will give tablespace wise space used by objects and total size
of each tablespace in MB and the respective percentage

select x.tablespace_name, x.used_size_in_mb, y.total_size_in_mb,
( x.used_size_in_mb /  y.total_size_in_mb ) * 100 used_space_percentage
from (
select tablespace_name, sum(bytes)/1024/1024 used_size_in_mb
from dba_segments
group by tablespace_name ) x,  
( select tablespace_name, sum(bytes)/1024/1024 total_size_in_mb
from dba_data_files
group by tablespace_name ) y
where x.tablespace_name = y.tablespace_name;
Naveen KumarProduction Manager / Application Support ManagerCommented:
This is to get the all objects and their space allocated in a particular tablespace.

select segment_name, bytes/1024/1024 size_in_mb
from dba_segments
where tablespace_name = 'USER_DATA';
It is a hard task to estimate the size used by objects.

set echo off
-- Script to determine objects in a specified tablespace

accept tableSpaceName prompt 'Enter tablespace: '
set linesize 132
set pagesize 72
col type format a17
heading "object type"
col object format a111 heading "schema.Object"

break on type skip 1 on type
ttitle left 'Tablespace:
' &tableSpaceName - skip 1 ' '
select segment_type type,
     rtrim(owner)||'.'||rtrim(segment_name) object
where file_id in (
     select file_id
     from sys.dba_data_files
     where tablespace_name =
group by owner, segment_name, segment_type
order by segment_type;

set echo on

The script lists the total size of all datafiles, grouped by the month in which they were created.

 select to_char(creation_time, 'RRRR Month') "Month",
"Growth in Meg"
    from sys.v_$datafile
    where creation_time > SYSDATE-365
    group by to_char(creation_time, 'RRRR
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.