Sunday, 23 September 2012

Oracle Database: Find Free and Used space

SELECT Total.name "Tablespace Name",
       round(nvl(Free_space, 0)/1024) "Free Size(GB)",
       round(nvl(total_space-Free_space, 0)/1024) "Used Size(GB)",
       round(total_space/1024) "Total Size(GB)"
FROM
  (select tablespace_name, sum(bytes/1024/1024) free_space
     from sys.dba_free_space
    group by tablespace_name
  ) Free,
  (select b.name,  sum(bytes/1024/1024) total_space
     from sys.v_$datafile a, sys.v_$tablespace B
    where a.ts# = b.ts#
    group by b.name
  ) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name;

No comments:

Post a Comment

Number of Visitors