How to Show Used/Free Tablespace Space in Oracle DB

This script will list tablespace wise free space and used space (also total size) as well as total space. For running this query you must have SELECT privileges to SYS.DBA_FREE_SPACE, SYS.V_$DATAFILE, SYS.V_$TABLESPACE views.


SELECT Total.name "Tablespace Name",
       nvl(Free_space, 0) "Free Size(MB)",
       nvl(total_space-Free_space, 0) "Used Size(MB)", 
       total_space "Total Size(MB)"
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;

Comments

Popular posts from this blog

Create Tables in Oracle Database using TOAD

How to create Oracle stored Procedures using TOAD for Oracle

How to Create Packages in Oracle Database using TOAD for Oracle