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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  amp  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Here is a query used by Oracle SQL Developer in its Tablespaces view select a tablespace name as  quot Tablespace Name quot          round a bytes alloc   1024   1024   quot Allocated  MB  quot          round nvl b bytes free  0    1024   1024   quot Free  MB  quot          round  a bytes alloc - nvl b bytes free  0     1024   1024   quot Used  MB  quot          round  nvl b bytes free  0    a bytes alloc    100   quot   Free quot          100 - round  nvl b bytes free  0    a bytes alloc    100   quot   Used quot          round maxbytes 1024   1024   quot Max  Bytes  MB  quot  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

User · Answer

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

[oracle] Find out free space on tablespace

Examples related to oracle

Examples related to tablespace