Get the TableSpace usage in Oracle

1.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’

 

2.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;