SQL QUERY TO FIND TABLESPACE FREE SPACE IN ORACLE

SQL QUERY TO FIND TABLESPACE FREE SPACE IN ORACLE

Use this query to filter the tablespace which has space crunch

set lines 200  pages 1000
col TABLESPACE_NAME for a20
col TOTAL_FREE_SPACE_MB for 999999
col TOTAL_SPACE_MB for 999999
col TBLSPC for a20
with TABLESPACE_V 
as 
(
select x.TABLESPACE_NAME as TBLSPC,
            round(sum(x.bytes/1048576)) as TOTAL_SPACE_MB
from 
dba_data_files x 
group by x.TABLESPACE_NAME
order by 1
),
SEGMENT_V as 
(
select y.tablespace_name as TBLSEG,
            round(sum(y.bytes/1048576)) as TOTAL_SIZE_OCCUPIED
from dba_segments y
group by y.tablespace_name
order by 1
)
select TBLSPC,
           TOTAL_SPACE_MB,
           sum(TOTAL_SPACE_MB - TOTAL_SIZE_OCCUPIED) as TOTAL_FREE_SPACE,
           round(100*((TOTAL_SPACE_MB - TOTAL_SIZE_OCCUPIED) / TOTAL_SPACE_MB)) as PCT_F
from 
TABLESPACE_V a
inner join SEGMENT_V b on (a.TBLSPC=b.TBLSEG)
group by TBLSPC,
TOTAL_SPACE_MB,
round(100*((TOTAL_SPACE_MB - TOTAL_SIZE_OCCUPIED) / TOTAL_SPACE_MB))
order by 4 desc;

If you want to specifically filter only the tablespaces with a specific condition with less space, then use this query

set lines 200  pages 1000
col TABLESPACE_NAME for a20
col TOTAL_FREE_SPACE_MB for 999999
col TOTAL_SPACE_MB for 999999
col TBLSPC for a20
with TABLESPACE_V 
as 
(
select x.TABLESPACE_NAME as TBLSPC,
            round(sum(x.bytes/1048576)) as TOTAL_SPACE_MB
from 
dba_data_files x 
group by x.TABLESPACE_NAME
order by 1
),
SEGMENT_V as 
(
select y.tablespace_name as TBLSEG,
            round(sum(y.bytes/1048576)) as 
           TOTAL_SIZE_OCCUPIED
from dba_segments y
group by y.tablespace_name
order by 1
)
select TBLSPC,
           TOTAL_SPACE_MB,
           sum(TOTAL_SPACE_MB - TOTAL_SIZE_OCCUPIED) as TOTAL_FREE_SPACE,
           round(100*((TOTAL_SPACE_MB - TOTAL_SIZE_OCCUPIED) / TOTAL_SPACE_MB)) as PCT_F
from 
TABLESPACE_V a
inner join SEGMENT_V b on (a.TBLSPC=b.TBLSEG)
group by TBLSPC,
TOTAL_SPACE_MB,
round(100*((TOTAL_SPACE_MB - TOTAL_SIZE_OCCUPIED) / TOTAL_SPACE_MB))
     having 
sum(TOTAL_SPACE_MB - TOTAL_SIZE_OCCUPIED) < 10 
     and
round(100*((TOTAL_SPACE_MB - TOTAL_SIZE_OCCUPIED) / TOTAL_SPACE_MB))  < 5
order by 4 desc;

Sample outputs:

SQL> set lines 200  pages 1000
col TABLESPACE_NAME for a20
SQL> SQL> col TOTAL_FREE_SPACE_MB for 99999999999
with TABLESPACE_V
as
(
select x.TABLESPACE_NAME as TBLSPC,
round(sum(x.bytes/1048576)) as TOTAL_SPACE_MB
from
dba_data_files x
group by x.TABLESPACE_NAME
order by 1
),
SEGMENT_V as
(
select y.tablespace_name as TBLSEG,round(sum(y.bytes/1048576)) as TOTAL_SIZE_OCCUPIED
from dba_segments y
group by y.tablespace_name
order by 1
)
select TBLSPC,
TOTAL_SPACE_MB,
sum(TOTAL_SPACE_MB - TOTAL_SIZE_OCCUPIED) as TOTAL_FREE_SPACE,
round(100*((TOTAL_SPACE_MB - TOTAL_SIZE_OCCUPIED) / TOTAL_SPACE_MB)) as PCT_F
from
TABLESPACE_V a
inner join SEGMENT_V b on (a.TBLSPC=b.TBLSEG)
group by TBLSPC,
TOTAL_SPACE_MB,
round(100*((TOTAL_SPACE_MB - TOTAL_SIZE_OCCUPIED) / TOTAL_SPACE_MB))
order by 4 desc;SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28

TBLSPC                         TOTAL_SPACE_MB TOTAL_FREE_SPACE        PCT_F
------------------------------ -------------- ---------------- ----------
RCAT                                      900              893         99
SYSAUX                                    570               34          6
USERS                                     495               25          5
UNDOTBS1                                  270                6          2
SYSTEM                                    770               14          2

Leave a Reply

%d