Hey guys and friends ,
Today I write two kinds of SQL statements for checking all of user tablespaces' used percent (not including TEMP tablespaces) and I respectively run it on my same Oracle DB server.
As a result It has returned two different query results, hence I have a bit of confusion. The following steps are my operation process.
set linesize 200
set pagesize 200
col ts_name for a25
col used_pct(%) for 999.99
select ddf.tablespace_name ts_name
, sum(ddf.bytes)/1024/1024 total_mb
, sum(dfs.bytes)/1024/1024 free_mb
, round((1-(sum(dfs.bytes)/1024/1024)/(sum(ddf.bytes)/1024/1024))*100, 2) "used_pct(%)"
from dba_data_files ddf
, dba_free_space dfs
where ddf.tablespace_name = dfs.tablespace_name
group by ddf.tablespace_name
order by 4 desc;
TS_NAME TOTAL_MB FREE_MB used_pct(%)
------------------------- ---------- ---------- -----------
UNDOTBS1 503177373 105681.375 99.98
USERS 57624.75 104.0625 99.82
SZD_BASE_V2 4138196800 9324353.06 99.77
SYSAUX 50278231.3 227808 99.55
SYSTEM 24576 10900.625 55.65
SZD_PERFORMANCE_V2 4096 2412 41.11
SZD_BAR_V2 9216 7530 18.29
MIS_BASE 2000 1996 .20
8 rows selected.
set linesize 200
set pagesize 200
col ts_name for a25
col used_pct for a8
select total.tablespace_name ts_name,
round(total.mb, 2) as total_mb,
round(total.mb - free.mb, 2) as used_mb,
round(( 1 - free.mb / total.mb ) * 100, 2)
|| '%' as used_pct
from (select tablespace_name,
sum(bytes) / 1024 / 1024 as mb
from dba_free_space
group by tablespace_name) free,
(select tablespace_name,
sum(bytes) / 1024 / 1024 as mb
from dba_data_files
group by tablespace_name) total
where free.tablespace_name = total.tablespace_name
order by 4 desc;
TS_NAME TOTAL_MB USED_MB USED_PCT
------------------------- ---------- ---------- --------
USERS 2134.25 2030.19 95.12%
SYSAUX 193377.81 155409.81 80.37%
SZD_PERFORMANCE_V2 2048 842 41.11%
SZD_BASE_V2 647200 203183.19 31.39%
SZD_BAR_V2 3072 562 18.29%
UNDOTBS1 57069 10357 18.15%
SYSTEM 6144 693.69 11.29%
MIS_BASE 1000 2 .2%
8 rows selected.
Next I calculate the total sizes of some tablespaces with all of datafiles,
[oracle@xxxx]$ ls -lrth sysaux0*
-rw-r----- 1 oracle oinstall 32G May 7 14:06 sysaux04.dbf
-rw-r----- 1 oracle oinstall 32G May 7 14:06 sysaux05.dbf
-rw-r----- 1 oracle oinstall 32G May 7 14:52 sysaux01.dbf
-rw-r----- 1 oracle oinstall 32G May 7 14:55 sysaux03.dbf
-rw-r----- 1 oracle oinstall 32G May 7 14:55 sysaux02.dbf
-rw-r----- 1 oracle oinstall 32G May 7 14:55 sysaux06.dbf
[oracle@xxxx]$ ls -lrth sysaux0* | awk '{print $5}' | awk '{SUM += $1} END {print SUM}'
192
[oracle@xxxx]$ ls -lrth system0*
-rw-r----- 1 oracle oinstall 1.1G May 7 13:56 system02.dbf
-rw-r----- 1 oracle oinstall 5.1G May 7 14:58 system01.dbf
[oracle@xxxx]$ ls -lrth system0* | awk '{print $5}' | awk '{SUM += $1} END {print SUM}'
6.2
So I found my first SQL statement should be wrong. It's why?
Could you help me? Thanks in advance!
Best Regards
Quanwen Zhao