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 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;

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 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))
 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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s