Example query to check free,used space and Percent used per tablespace:
select a.TABLESPACE_NAME,
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc
Sample output:
TABLESPACE_NAME BYTES_USED BYTES_FREE LARGEST PERCENT_USED ------------------------------ ---------- ---------- ---------- ------------ SYSTEM 1101004800 12648448 9371648 98.85 USERS 4806082560 311296000 152043520 93.52 SYSAUX 1145372672 77856768 53739520 93.2 EXAMPLE 104857600 23724032 20905984 77.38 AG_INDX2 5368709120 1396768768 320864256 73.98 AG_DATA1 5368709120 1641086976 506462208 69.43 AG_INDX3 10737418240 3517972480 901120000 67.24 AG_INDX1 5368709120 1947795456 586219520 63.72 AG_DATA2 10737418240 4090298368 987103232 61.91 AG_DATA3 10737418240 5334171648 1006632960 50.32 AG_DATA4 5368709120 2759720960 339738624 48.6 AG_INDX4 5368709120 4873191424 3503292416 9.23 UNDOTBS1 2034237440 2020278272 1898840064 0.69
thanks dear for sharing this script.