gopal_1980

 Profile Answers by gopal_1980 

• Apr 5th, 2010

 

We can get tablespace free space from DBA_FREE_SPACE

aravindhreturns

 Profile Answers by aravindhreturns 

• Apr 22nd, 2010

 

The total size of the tablespace can be determined through DBA_DATA_FILES.
Likewise, free size of the tablespace can be determined through DBA_FREE_SPACE.

So you can simply do this,
Subract you can simple subtract the values from DBA_DATA_FILES with DBA_FREE_SPACE to get the currently usable/remaning space.

Here, Find the query attached:-

SELECT a.tablespace_name,
a.bytes / 1024 / 1024 total_mb,
b.bytes / 1024 / 1024 used_mb,
( a.bytes / 1024 / 1024 ) - ( b.bytes / 1024 / 1024 ) free_mb
FROM (SELECT tablespace_name,
SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
SUM(bytes) bytes
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
ORDER BY free_mb DESC