Sql Statement We Use to Generate Tablespace Usage Report

A Sql Statement We Use to Generate Tablespace Usage Report Taking Autoextend Into Consideration

This article describes the query we created in order to generate Database level Tablespace usage in percentage with Autoextend on.
In past we used a query which was showing total assigned space to a particular tablespace and was not including future growth when Autoextend On.

So it was giving wrong numbers:
:-> Example: Size of tablespace is 1GB – Used Space is 900MB – Free Space is 100MB –

Now it has Autoextend On with 4GB Total extension capabilities.

It was sending us Paging telling only 10 Percentage free –instead it should take Total 4GB in consideration and by that it has total 3.1GB Free. Soo we dug into the data dictionary and found base sys views/tables from where we can get that information and refined our query with the same.

Query:

REM Following Sql statement will generate tablespace usage report taking autoextend into consideration
sqlplus "/ as sysdba"
set linesize 250
set pagesize 70
col tablespace_name format a20
select a.tablespace_name, SUM(a.bytes)/1024/1024 "CurMb",
SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "MaxMb",
(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "TotalUsed",
(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "TotalFree",
round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent"
from dba_data_files a,
sys.filext$ b,
(SELECT c.tablespace_name , sum(c.bytes) "Free" FROM DBA_FREE_SPACE c GROUP BY TABLESPACE_NAME) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"/1024
/
exit;