Logo Background

Total DB Tablespace Size

  • By on November 14, 2008 | No Comments

    Controlling and monitoring of database tablespace was a crucial job for a database administrator. When your database table runs out of tablespace, the user will faces error when submitting concurrent request that involve CREATE and INSERT statement.

    SQL> SELECT SPACE.tablespace_name, SPACE.total_space, free.total_free,
    ROUND(free.total_free/SPACE.total_space*100) AS pct_free,
    ROUND((SPACE.total_space-free.total_free),2) AS total_used,
    ROUND((SPACE.total_space-free.total_free)/SPACE.total_space*100) AS pct_used,
    free.max_free, NEXT.max_next_extent
    FROM
    (SELECT tablespace_name, SUM(bytes)/1024/1024 total_space
    FROM dba_data_files
    GROUP BY tablespace_name) SPACE,
    (SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free,
    ROUND(MAX(bytes)/1024/1024,2) max_free
    FROM dba_free_space
    GROUP BY tablespace_name) free,
    (SELECT tablespace_name, ROUND(MAX(next_extent)/1024/1024,2) max_next_extent
    FROM dba_segments
    GROUP BY tablespace_name) NEXT
    WHERE SPACE.tablespace_name = free.tablespace_name (+)
    AND SPACE.tablespace_name = NEXT.tablespace_name (+)
    AND (ROUND(free.total_free/SPACE.total_space*100)<> free.max_free)
    ORDER BY pct_used DESC;

    This script will return the total tablespace usage information.

    SQL> SELECT tablespace_name, bytes/1024/1024 FROM dba_free_space;

    This script will return the free space left in the tablespace information

    SQL> SELECT file_name, SUM(bytes)/(1024*1024) FROM dba_temp_files
    GROUP BY file_name;
    SQL> SELECT tablespace_name, SUM(bytes)/(1024*1024) TEMPSIZE FROM dba_temp_files
    GROUP BY tablespace_name;

    This 2 script will return the usage of TEMP tablespace. The TEMP tablespace will be used if your SQL is running with group by, order by, nested select joining which requires TEMP tablespace for this sorting purposes

    SQL> SELECT a.data_size+b.temp_size+c.redo_size "total_size"
    FROM ( SELECT SUM(bytes) data_size
    FROM dba_data_files ) a,
    ( SELECT nvl(SUM(bytes),0) temp_size
    FROM dba_temp_files ) b,
    ( SELECT SUM(bytes) redo_size
    FROM sys.v_$log ) c;

    This script can be used to fetch the total size of data files allocated for database system

    SQL> SELECT tbs.tablespace_name,
    tot.bytes/(1024*1024) "Total Space in MB",
    round(tot.bytes/(1024*1024)- SUM(nvl(fre.bytes,0))/(1024*1024),2) "Used in MB",
    round(SUM(nvl(fre.bytes,0))/(1024*1024),2) "Free in MB",
    round((1-SUM(nvl(fre.bytes,0))/tot.bytes)*100,2) Pct,
    decode(
    greatest((1-SUM(nvl(fre.bytes,0))/tot.bytes)*100, 90),
    90, '', '*'
    ) Pct_warn
    FROM dba_free_space fre,
    (SELECT tablespace_name, SUM(bytes) bytes
    FROM dba_data_files
    GROUP BY tablespace_name) tot,
    dba_tablespaces tbs
    WHERE tot.tablespace_name = tbs.tablespace_name
    AND fre.tablespace_name(+) = tbs.tablespace_name
    GROUP BY tbs.tablespace_name, tot.bytes/(1024*1024), tot.bytes
    ORDER BY 5 DESC, 1;

    This scripts gives warning indicator for all tablespaces that have less then 90% free space in them with an asterisk in the last column.

    Previous
    Next
    » Database Session Memory Usage
Leave a Comment