I need to know true tablespace size in Oracle. I have some tablespace and I need to know how many space it uses now and how many space is free (and maybe percent of free space). I found in web some sqls but all of them showed size based on water mark... which is not true space allocated now but as far as I know the highest value which has ever been reached... So my real need is to know if I have enough space for my data which constantly are written and I must know how much of them I can store before having to delete some of them.
Thanks
Try this:
-- Available space, by tablespace
SELECT * FROM
(SELECT tablespace_name FROM dba_tablespaces)
LEFT OUTER JOIN
(SELECT tablespace_name, SUM(bytes) AS total_bytes
FROM dba_data_files
GROUP BY tablespace_name)
USING (tablespace_name)
LEFT OUTER JOIN
(SELECT tablespace_name, sum(bytes) AS used_bytes
from dba_segments
GROUP BY tablespace_name)
USING (tablespace_name)
LEFT OUTER JOIN
(SELECT tablespace_name, SUM(bytes) AS free_bytes
FROM dba_free_space
GROUP BY tablespace_name)
USING (tablespace_name);