table space usage script required.

Posted on 2007-07-24
Last Modified: 2013-12-19
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.
Question by:YaHozna
    LVL 28

    Assisted Solution

    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';
    LVL 28

    Accepted Solution

    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;
    LVL 47

    Assisted Solution

    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

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now