How to get the TableSpace usage in Oracle

To get the table space usage in oracle use this query

select    a.TABLESPACE_NAME,
ROUND(a.BYTES/1024000) “Used (MB)”,
ROUND(b.BYTES/1024000) “Free (MB)”,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) “% USED”
from
(
select  TABLESPACE_NAME,
sum(BYTES) BYTES
from    dba_data_files
group   by TABLESPACE_NAME
)
a,
(
select  TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from    dba_free_space
group   by TABLESPACE_NAME
)
b
where    a.TABLESPACE_NAME=b.TABLESPACE_NAME
and a.TABLESPACE_NAME like ‘%’
order      by ((a.BYTES-b.BYTES)/a.BYTES) desc ;

If You want to list a particular table space  replace  a.TABLESPACE_NAME like ‘%’ with a.TABLESPACE_NAME like ‘MY_TABLE_SPACE’

To get the temporary tablespace usuage use this query

SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free)
FROM V$temp_space_header
GROUP BY tablespace_name;

3 Replies to “How to get the TableSpace usage in Oracle”

  1. Wohh exactly what I became looking for, thankyou with regard to submitting . “Talent grows throughout pleasure, persona in the complete existing involving man life.” by simply Johann Wolfgang von Goethe.

Leave a Reply

Your email address will not be published.