Pages

Monday, 4 March 2013

How to check tablespace usage


SELECT
  tablespace_name "Tablespace",
  TO_CHAR((a.bytes / 1048576),'99,999,990.900') "Total MB",
  TO_CHAR(((a.bytes - DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576),'99,999,990.900') "Used MB",
  TO_CHAR((((a.bytes - DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576)*100) / (a.bytes / 1048576),'999,999.9') "% Used"
FROM
  (sys.dba_tablespaces d JOIN sys.sm$ts_avail a USING (tablespace_name))
  LEFT OUTER JOIN sys.sm$ts_free f USING (tablespace_name)
  where tablespace_name in ('xxx')
ORDER BY 4;

No comments:

Post a Comment