Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1045
  • Last Modified:

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.
  • 2
3 Solutions
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';
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;
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now