True tablespace size in oracle

Question

I need to know the true size of the table space in Oracle. I have some table space, and I need to know how much space it is using now and how much free space (and maybe a percentage of free space). I found several sqls on the Internet, but they all showed the size based on the sign of water ... which is not a true space right now, but as far as I know the highest value that has ever been achieved ... Therefore, I really need to know if whether I have enough space for my data that is constantly being written, and I need to know how many of them I can save before deleting some of them.

thanks

Answer 1

Try the following:

-- 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); 

Answer 2

If you want to get an idea of ​​the size of the data file, including those files that can automatically expand, try:

 SELECT DISTINCT a.tablespace_name, sum(a.bytes)/1024/1024 CurMb, sum(decode(b.maxextend, null, a.bytes/1024/1024, b.maxextend*(SELECT value FROM v$parameter WHERE name='db_block_size')/1024/1024)) MaxMb, round(100*(sum(a.bytes)/1024/1024 - round(c.free/1024/1024))/(sum(decode(b.maxextend, null, a.bytes/1024/1024, b.maxextend*(SELECT value FROM v$parameter WHERE name='db_block_size')/1024/1024)))) UPercent, (sum(a.bytes)/1024/1024 - round(c.free/1024/1024)) TotalUsed, (sum(decode(b.maxextend, null, a.bytes/1024/1024, b.maxextend*(SELECT value FROM v$parameter WHERE name='db_block_size')/1024/1024)) - (sum(a.bytes)/1024/1024 - round(c.Free/1024/1024))) TotalFree 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 

Answer 3

Hope this helps you

  SELECT a.tablespace_name, a.file_name, a.bytes allocated_bytes,b.free_bytes FROM dba_data_files a, (SELECT file_id, SUM(bytes) free_bytes FROM dba_free_space b GROUP BY file_id) b WHERE a.file_id=b.file_id ORDER BY a.tablespace_name;