Sine i need to check the maximum size of tablespace with autoextend enabled i write the following query:
set linesize 100
set pagesize 100
select
a.tablespace_name,
round(SUM(a.bytes)/(1024*1024*1024)) CURRENT_GB,
round(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),
b.maxextend*8192/(1024*1024*1024)))) MAX_GB,
(SUM(a.bytes)/(1024*1024*1024) - round(c.Free/1024/1024/1024)) USED_GB,
round((SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),
b.maxextend*8192/(1024*1024*1024))) - (SUM(a.bytes)/(1024*1024*1024) -
round(c.Free/1024/1024/1024))),2) FREE_GB,
round(100*(SUM(a.bytes)/(1024*1024*1024) -
round(c.Free/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),
b.maxextend*8192/(1024*1024*1024))))) USED_PCT
from
dba_data_files a,
sys.filext$ b,
(SELECT
d.tablespace_name ,sum(nvl(c.bytes,0)) Free
FROM
dba_tablespaces d,
DBA_FREE_SPACE c
WHERE
d.tablespace_name = c.tablespace_name(+)
group by d.tablespace_name) c
WHERE
a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name, c.Free/1024
ORDER BY tablespace_name;
The result looks like this:
TABLESPACE_NAME CURRENT_GB MAX_GB USED_GB FREE_GB USED_PCT ------------------------------ ---------- ---------- ---------- ---------- ---------- SYSAUX 16 32 1 31 3 SYSTEM 16 32 0 32 0 TEST 10 11 0 10.85 0 UNDOTBS1 16 32768 0 32768 0 UNDOTBS2 16 32768 0 32768 0 UNDOTBS3 16 32768 0 32768 0 UNDOTBS4 16 32768 0 32768 0 UNDOTBS5 16 32768 0 32768 0 UNDOTBS6 16 32768 0 32768 0 UNDOTBS7 16 32768 0 32768 0 UNDOTBS8 16 32768 0 32768 0 USERS 1 32 0 32 0
Great Thanks for this query I was in search of this query for long back.
Great I would be better if you could have taken temp tablespace also into consideration
It’s a great script but when I run it on my database, I get negative values for USED_GB and the USED_PCT on some tablespaces.
Hi Hugo,
Old post, but I encountered the same issue with this query.
The problem is when you have a datafile with a MAXBYTES inferior to BYTES and autoextend is ON.
You can modify the query with “GREATEST (BYTES, b.maxextend*8192)” when needed.
For example:
“round(100*(SUM(a.bytes)/(1024*1024*1024) –
round(c.Free/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),GREATEST (b.maxextend*8192/(1024*1024*1024),A.BYTES/(1024*1024*1024)))))) USED_PCT”
Antoine
select * from dba_data_files;
you can also see the above command to check the datafile has Autoextend enable are not