Find out free space on tablespace

Question

Our application has failed a few times because an 'ORA-01536: space quota exceeded for tablespace', and we would like to be able to prevent this by checking regularly the free space on the tablespace and raising an alert when it drops below certain level.

Is there any way to find out how much free space is left in a tablespace?

After some research (I am not a DBA), I tried the following:

select max_bytes-bytes from user_ts_quotas;

select sum(nvl(bytes,0)) from user_free_space;

but those queries return completely different results.

score 116 · Accepted Answer · answered Oct 06 '11 at 09:00

116

I use this query

column "Tablespace" format a13
column "Used MB"    format 99,999,999
column "Free MB"    format 99,999,999
column "Total MB"   format 99,999,999
select
   fs.tablespace_name                          "Tablespace",
   (df.totalspace - fs.freespace)              "Used MB",
   fs.freespace                                "Free MB",
   df.totalspace                               "Total MB",
   round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) TotalSpace
   from
      dba_data_files
   group by
      tablespace_name
   ) df,
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) FreeSpace
   from
      dba_free_space
   group by
      tablespace_name
   ) fs
where
   df.tablespace_name = fs.tablespace_name;

answered Oct 06 '11 at 09:00

yanjost

5,223
2
25
28

1

Nice - this gives the same number as Enterprise manager; awesome! I trust this now =) – Ryan S Feb 23 '15 at 16:57
I tried it and it seems that the list will not include a completely full tablespace because there is no entry for it in the dba_free_space. (I'm on 10g) – xtsoler Jan 17 '17 at 09:11
The query works assuming that you have the necessary privileges to access DBA tables. – Bando Aug 22 '22 at 15:45

score 38 · Answer 2 · answered Mar 22 '12 at 05:04

38

A much more accurate SQL STATEMENT

SELECT  a.tablespace_name,
    ROUND (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES) * 100,2) percentage_used,
    c.BYTES / 1024 / 1024 space_allocated,
    ROUND (c.BYTES / 1024 / 1024 - NVL (b.BYTES, 0) / 1024 / 1024,2) space_used,
    ROUND (NVL (b.BYTES, 0) / 1024 / 1024, 2) space_free, 
    c.DATAFILES
  FROM dba_tablespaces a,
       (    SELECT   tablespace_name, 
                  SUM (BYTES) BYTES
           FROM   dba_free_space
       GROUP BY   tablespace_name
       ) b,
      (    SELECT   COUNT (1) DATAFILES, 
                  SUM (BYTES) BYTES, 
                  tablespace_name
           FROM   dba_data_files
       GROUP BY   tablespace_name
    ) c
  WHERE b.tablespace_name(+) = a.tablespace_name 
    AND c.tablespace_name(+) = a.tablespace_name
ORDER BY NVL (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES), 0) DESC;

answered Mar 22 '12 at 05:04

better sql statement

381
3
2

2

this one has a TEMP tablespace, which wasn't displayed when using the query from the answer – Denys May 08 '14 at 08:57
I personally check that this is pretty fast. 6 seconds vs 188 seconds that have a similar query that we have being used from some time ago., on a Oracle 12 database. – Zardoz89 Dec 30 '14 at 15:43
I'm sorry, But what is the unit of size we are speaking here..? – Raja Anbazhagan Mar 15 '15 at 14:32
@RajaAnbazhagan - units here are MB = bytes / 1024 / 1024 – AngelWarrior Aug 19 '15 at 03:40

score 19 · Answer 3 · edited Dec 31 '14 at 07:42

There are many ways to check the size, but as a developer we dont have much access to query meta tables, I find this solution very easy (Note: if you are getting error message ORA-01653 ‘The ORA-01653 error is caused because you need to add space to a tablespace.’)

--Size of All Table Space

--1. Used Space
SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS "USED SPACE(IN GB)" FROM USER_SEGMENTS GROUP BY TABLESPACE_NAME
--2. Free Space
SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS "FREE SPACE(IN GB)" FROM   USER_FREE_SPACE GROUP BY TABLESPACE_NAME

--3. Both Free & Used
SELECT USED.TABLESPACE_NAME, USED.USED_BYTES AS "USED SPACE(IN GB)",  FREE.FREE_BYTES AS "FREE SPACE(IN GB)"
FROM
(SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS USED_BYTES FROM USER_SEGMENTS GROUP BY TABLESPACE_NAME) USED
INNER JOIN
(SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS FREE_BYTES FROM  USER_FREE_SPACE GROUP BY TABLESPACE_NAME) FREE
ON (USED.TABLESPACE_NAME = FREE.TABLESPACE_NAME);

Thanks

score 13 · Answer 4 · edited Feb 25 '13 at 12:27

13

This is one of the simplest query for the same that I came across and we use it for monitoring as well:

SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024/1024 "FREE SPACE(GB)"
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

A complete article about Oracle Tablespace: Tablespace

edited Feb 25 '13 at 12:27

andy

5,979
2
27
49

answered Feb 25 '13 at 12:09

Ankit Agrawal

131
1
2

I tried your solution in my database and it failed. – Momergil Oct 01 '18 at 15:10
What is the exact error message? Try to execute this command as system user (or with a user that was granted to view dba_data_files). As an alternative you can try to select the data from "user_free_space" as mentioned by "@Gurupreet Singh Bhatia" – molnarg Jul 22 '22 at 08:20

score 6 · Answer 5 · answered Jan 15 '21 at 03:24

this is pretty good as well


clear breaks
clear computes

Prompt
Prompt Tablespace Usage
Prompt

SET lines 120 pages 500 
col percent_used format 999.99

SELECT a.TABLESPACE_NAME,
  NVL(ROUND((a.BYTES  /1024)/1024/1024,2),2) GB_TOTAL,
  NVL(ROUND((b.BYTES  /1024)/1024/1024,2),2) GB_FREE,
  NVL(ROUND((b.largest/1024),2),0) KB_Chunk,
  NVL(ROUND(((a.BYTES -NVL(b.BYTES,1))/a.BYTES)*100,4),0) percent_used
FROM
  (SELECT TABLESPACE_NAME,
    NVL(SUM(BYTES),0) BYTES
  FROM dba_data_files
  GROUP BY TABLESPACE_NAME
  ) a,
  (SELECT TABLESPACE_NAME,
    NVL(SUM(BYTES),1) BYTES ,
    NVL(MAX(BYTES),1) largest
  FROM dba_free_space
  GROUP BY TABLESPACE_NAME
  ) b
WHERE a.TABLESPACE_NAME=b.TABLESPACE_NAME(+)
ORDER BY ((a.BYTES-b.BYTES)/a.BYTES) DESC;

output

TABLESPACE_NAME                  GB_TOTAL    GB_FREE   KB_CHUNK PERCENT_USED
------------------------------ ---------- ---------- ---------- ------------
SYSTEM                                .84        .02       9216        97.36
SYSAUX                                .57        .05      32768        91.10
UNDOTBS1                              .06        .05      36864        23.13
USERS                                   0          0       4096        20.00

score 2 · Answer 6 · edited Aug 20 '20 at 08:14

2

The following query will help to find out free space of tablespaces in MB:

select tablespace_name , sum(bytes)/1024/1024 from dba_free_space group by tablespacE_name order by 1;

edited Aug 20 '20 at 08:14

marsh-wiggle

2,508
3
35
52

answered Aug 19 '20 at 17:53

kandarp sarvaiya

61
1
5

How does this differ from the already given answer?: https://stackoverflow.com/a/15066653/1574221 – marsh-wiggle Aug 19 '20 at 19:46
1

Welcome to StackOverflow. As mentioned by @marsh-wiggle, please provide explanation why your answer is better/different from the already provided answers. – AdityaKeyal Aug 20 '20 at 00:39

score 2 · Answer 7 · edited Mar 10 '22 at 11:42

You can check the tablespace free space by running this query :

SELECT dfq.tablespace_name AS "Tablespace Name",
       dfq.totalspace AS "Total Size MB",
       (dfq.totalspace - dsq.totalusedspace) "Free Space MB",
       ROUND(100 * ((dfq.totalspace - dsq.totalusedspace) / dfq.totalspace))||'%' "Free Space %"
  FROM (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) totalspace
          FROM dba_data_files
         GROUP BY tablespace_name) dfq,
       (SELECT tablespace_name,
               ROUND(SUM(bytes) / (1024 * 1024)) AS totalusedspace
          FROM dba_segments
         GROUP BY tablespace_name) dsq
 WHERE dfq.tablespace_name = dsq.tablespace_name(+);

In above query we have selected the total size of each tablespace as well as the free space in MB and the free space in percentage from the total size. So you can use this percentage column to track and raising an alert when the free space percentage reachs 20% or whatever the percentage you want to be.

score 1 · Answer 8 · edited Aug 04 '16 at 04:49

Unless I'm mistaken, the above code does not take unallocated space into account, so if you really want to know when you'll hit a hard limit, you should use maxbytes.

I think the code below does that. It calculates free space as "freespace" + unallocated space.

select 
     free.tablespace_name,
     free.bytes,
     reserv.maxbytes,
     reserv.bytes,
     reserv.maxbytes - reserv.bytes + free.bytes "max free bytes",
     reserv.datafiles
from
    (select tablespace_name, count(1) datafiles, sum(maxbytes) maxbytes, sum(bytes) bytes from dba_data_files group by tablespace_name) reserv,
    (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) free
where free.tablespace_name = reserv.tablespace_name;

score 1 · Answer 9 · answered Jan 13 '17 at 21:19

column pct_free format 999.99
select
     used.tablespace_name,
     (reserv.maxbytes - used.bytes)*100/reserv.maxbytes pct_free,
     used.bytes/1024/1024/1024 used_gb,
     reserv.maxbytes/1024/1024/1024 maxgb,
     reserv.bytes/1024/1024/1024 gb,
     (reserv.maxbytes - used.bytes)/1024/1024/1024 "max free bytes",
     reserv.datafiles
from
    (select tablespace_name, count(1) datafiles, sum(greatest(maxbytes,bytes)) maxbytes, sum(bytes) bytes from dba_data_files group by tablespace_name) reserv,
    (select tablespace_name, sum(bytes) bytes from dba_segments group by tablespace_name) used
where used.tablespace_name = reserv.tablespace_name
order by 2
/

Welcome to Stack Overflow! Code-only answers are not very helpful. Please edit your answer to explain why your code solves the original problem. — Joe C, Jan 13 '17 at 22:14

score 0 · Answer 10 · answered Jul 17 '20 at 10:39

Here is a query used by Oracle SQL Developer in its Tablespaces view

select a.tablespace_name as "Tablespace Name",
       round(a.bytes_alloc / 1024 / 1024) "Allocated (MB)",
       round(nvl(b.bytes_free, 0) / 1024 / 1024) "Free (MB)",
       round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) "Used (MB)",
       round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) "% Free",
       100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) "% Used",
       round(maxbytes/1024 / 1024) "Max. Bytes (MB)"
from  ( select  f.tablespace_name,
               sum(f.bytes) bytes_alloc,
               sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
        from dba_data_files f
        group by tablespace_name) a,
      ( select  f.tablespace_name,
               sum(f.bytes)  bytes_free
        from dba_free_space f
        group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
union all
select 
       h.tablespace_name as tablespace_name,
       round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
       round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576) megs_free,
       round(sum(nvl(p.bytes_used, 0))/ 1048576) megs_used,
       round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
       100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
       round(sum(f.maxbytes) / 1048576) max
from   sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f
where  p.file_id(+) = h.file_id
and    p.tablespace_name(+) = h.tablespace_name
and    f.file_id = h.file_id
and    f.tablespace_name = h.tablespace_name
group by h.tablespace_name
ORDER BY 2;

score -1 · Answer 11 · answered Feb 09 '19 at 07:54

You can also get a rough idea of table space usage by looking at the size of the files on your disk.

My DB is created with max extents, and each dbf file can only grow to 32gigs - so when the last one reaches 32gigs, you know you're about to run out of room and need to add another.

score -2 · Answer 12 · answered Mar 10 '14 at 10:44

-2

You can use a script called tablespaces.sh inside this helpful bundle: http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html

answered Mar 10 '14 at 10:44

user3401360

11

Find out free space on tablespace

12 Answers12

Linked