Hi
I am using Oracle 11g on Linux and trying to check my tablespace usage. I found following two queries from the internet and both are giving different results. My question is which one is the correct one?
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
order by 1 ;
The above query gives following result:
Tablespace Used MB Free MB Total MB Pct. Free
------------------------------ ---------- ---------- ---------- ----------
SYSAUX 3778 2930 6708 44
SYSTEM 1651 708 2359 30
UNDOTBS1 195 829 1024 81
USERS 2616 81 2697 3
select a.TABLESPACE_NAME tname,
ROUND(a.BYTES/1024000) MB_Used,
ROUND(b.BYTES/1024000) MB_Free,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) Percent_USED,
0 none
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 1 desc;
The above query gives following output:
TNAME MB_USED MB_FREE PERCENT_USED NONE
------------------------------ ---------- ---------- ------------ ----------
USERS 2762 977 64.63 0
UNDOTBS1 1049 848 19.17 0
SYSTEM 2416 723 70.05 0
SYSAUX 6869 2999 56.35 0