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
