Query to Check Tablespace Size and frees pace in Oracle Database

[Ahmad Mujeeb]

1.    To check the consolidated tablespace size and freespace:

             set pagesize 1000;
              set linesize 250;
              column total format a20
              column freespace format a20
              column tablespace_name format a25
  column file_name format a55

SELECT DFS.TABLESPACE,TOTAL,FREESPACE,TOTAL-FREESPACE "USED",((TOTAL-FREESPACE)*100)/TOTAL "PERCENTAGE USED"
               from  
      (SELECT  TABLESPACE_NAME "TABLESPACE"
            ,TO_CHAR(SUM(BYTES)/(1024*1024)) "TOTAL"
       FROM DBA_DATA_FILES
       --WHERE TABLESPACE_NAME='INVENTORY_UNIT'
       GROUP BY TABLESPACE_NAME
       ) DDF,
      (SELECT TABLESPACE_NAME "TABLESPACE"
       ,TO_CHAR(SUM(BYTES)/(1024*1024)) "FREESPACE"
       FROM DBA_FREE_SPACE      
       --WHERE TABLESPACE_NAME='INVENTORY_UNIT'
       GROUP BY TABLESPACE_NAME
       ) DFS
       where DDF.tablespace = DFS.tablespace
     order by DFS.tablespace;

2.    To check the each datafile size and freespace.

   SELECT TABLESPACE_NAME,
        file_name,
       (bytes/1024)/1024 "total(MB)",
       (free/1024)/1024 "free(MB)" ,
       ((free/1024)/1024) / ((bytes/1024)/1024)*100 "%Free"
  FROM dba_data_files a,
       (select file_id, sum(bytes) free from dba_free_space   group by file_id) b
WHERE a.file_id=b.file_id(+) ORDER BY TABLESPACE_NAME;


3.
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from
(select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name
UNION
select tablespace_name, sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b
where a.tablespace_name(+)=b.tablespace_name;

4.
col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;