How much of free space is available in individual tablespace in oracle

How much of free space is available in individual tablespace in oracle

If you are interested in investigating how much of free space is available in individual tablespace, here is the SQL query for this:

SQL>SELECT TABLESPACE_NAME,SUM(maxmb-cursize) FREE_MB

from

(

SELECT *

FROM (SELECT TABLESPACE_NAME,

FILE_NAME,

ROUND (BYTES / 1024 / 1024) cursize,

ROUND (MAXBYTES / 1024 / 1024) MaxMB,

AUTOEXTENSIBLE "Autoextend",

ROUND (

( ( (BYTES / 1024 / 1024) / (MAXBYTES / 1024 / 1024))),

2)

* 100

|| '%'

"Used"

FROM SYS.DBA_DATA_FILES

WHERE AUTOEXTENSIBLE = 'YES'

UNION

SELECT TABLESPACE_NAME,

FILE_NAME,

ROUND (BYTES / 1024 / 1024) cursize,

(CASE WHEN MAXBYTES = 0 THEN ROUND (BYTES / 1024 / 1024) END),

(CASE

WHEN AUTOEXTENSIBLE = 'NO'

THEN

'No:MaxOut per DBA setting'

END)

"Autoextensible",

'100%'

FROM SYS.DBA_DATA_FILES

WHERE AUTOEXTENSIBLE = 'NO')

WHERE tablespace_name = 'LAGOBLO'  ---replace LAGOBLO with your real tablespace_name

ORDER BY 1 ASC, 6 DESC

)

group by tablespace_name;

Output:

TABLESPACE_NAME                   FREE_GB
------------------------------                    -------------------
LAGOBLO                                             27