How to Check Tablespace in SQL

Below is the SQL Query to check for TableSpace in both GB(GigaBytes) & MB(MegaBytes)

SQL Query To check Tablespace Allocated Size and Free Size in GB

select b.tablespace_name, tbs_size SizeGB, a.free_space FreeGB from
(select tablespace_name, round(sum(bytes)/1024/1024/1024,1) as free_space
from dba_free_space group by tablespace_name UNION
select tablespace_name, round((free_space)/1024/1024/1024,1) as   free_space from dba_temp_free_space) a,
(select tablespace_name, sum(bytes)/1024/1024/1024 as tbs_size
from dba_data_files group by tablespace_name UNION
select tablespace_name, sum(bytes)/1024/1024/1024 tbs_size
from dba_temp_files group by tablespace_name ) b where a.tablespace_name(+)=b.tablespace_name;

 

OutPut:

TABLESPACE           SIZEGB                FREEGB
——————         ———-     —————————————
SYSAUX                          10                               8
SYSTEM                          24                              11
TEMP                               500                           230
UNDOTBS1                      255                            29
USERS                             36                               23

How to Check Tablespace Allocated Size and Free Size in MB

SQL Query To check Tablespace Allocated Size MB and Free Size MB and Percentage

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)) “Percentage 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;

 

Output:

TABLESPACE     UsedMB        FreeMB            TotalMB           Percentage Free
——————   ————     ———-        —————–     ——————–
SYSAUX                  366              32041              32674                          99
UNDOTBS1            107               65472              65344                         100
USERS                     7442            11118                18560                          60
SYSTEM                  309              4711                  5120                            94
TALLYDATA          39749          25784               65534                         39