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.
This question is related to
oracle
tablespace
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;
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;
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.
You can use a script called tablespaces.sh inside this helpful bundle: http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html
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;
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;
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;
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
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
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
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
/
Source: Stackoverflow.com