Home > Oracle in general > Query for Tablespace usage with Autoextend

Query for Tablespace usage with Autoextend

Sine i need to check the maximum size of tablespace with autoextend enabled i write the following query:

set linesize 100
set pagesize 100

select
                a.tablespace_name,
                round(SUM(a.bytes)/(1024*1024*1024)) CURRENT_GB,
                round(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024), 
                b.maxextend*8192/(1024*1024*1024)))) MAX_GB,
                (SUM(a.bytes)/(1024*1024*1024) - round(c.Free/1024/1024/1024)) USED_GB,
                round((SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024), 
                b.maxextend*8192/(1024*1024*1024))) - (SUM(a.bytes)/(1024*1024*1024) - 
                round(c.Free/1024/1024/1024))),2) FREE_GB,
                round(100*(SUM(a.bytes)/(1024*1024*1024) - 
                round(c.Free/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024), 
                b.maxextend*8192/(1024*1024*1024))))) USED_PCT
from
                dba_data_files a,
                sys.filext$ b,
                (SELECT
                               d.tablespace_name ,sum(nvl(c.bytes,0)) Free
                FROM
                               dba_tablespaces d,
                               DBA_FREE_SPACE c
                WHERE
                               d.tablespace_name = c.tablespace_name(+)
                               group by d.tablespace_name) c
WHERE
                a.file_id = b.file#(+)
                and a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name, c.Free/1024
ORDER BY tablespace_name;

The result looks like this:

TABLESPACE_NAME                CURRENT_GB     MAX_GB    USED_GB    FREE_GB   USED_PCT
------------------------------ ---------- ---------- ---------- ---------- ----------
SYSAUX                                 16         32          1         31          3
SYSTEM                                 16         32          0         32          0
TEST                                   10         11          0      10.85          0
UNDOTBS1                               16      32768          0      32768          0
UNDOTBS2                               16      32768          0      32768          0
UNDOTBS3                               16      32768          0      32768          0
UNDOTBS4                               16      32768          0      32768          0
UNDOTBS5                               16      32768          0      32768          0
UNDOTBS6                               16      32768          0      32768          0
UNDOTBS7                               16      32768          0      32768          0
UNDOTBS8                               16      32768          0      32768          0
USERS                                   1         32          0         32          0

 

Categories: Oracle in general Tags:
  1. Jamsher
    August 29th, 2012 at 16:58 | #1

    Great Thanks for this query I was in search of this query for long back.

  2. Mayank
    February 8th, 2013 at 13:38 | #2

    Great I would be better if you could have taken temp tablespace also into consideration

  3. hugo
    March 18th, 2013 at 14:16 | #3

    It’s a great script but when I run it on my database, I get negative values for USED_GB and the USED_PCT on some tablespaces.

    • Toine
      May 29th, 2018 at 14:50 | #4

      Hi Hugo,

      Old post, but I encountered the same issue with this query.
      The problem is when you have a datafile with a MAXBYTES inferior to BYTES and autoextend is ON.
      You can modify the query with “GREATEST (BYTES, b.maxextend*8192)” when needed.
      For example:
      “round(100*(SUM(a.bytes)/(1024*1024*1024) –
      round(c.Free/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),GREATEST (b.maxextend*8192/(1024*1024*1024),A.BYTES/(1024*1024*1024)))))) USED_PCT”

      Antoine

  4. Sathis Murali
    August 21st, 2017 at 06:38 | #5

    select * from dba_data_files;

    you can also see the above command to check the datafile has Autoextend enable are not

  1. No trackbacks yet.