What is
the query to find tablespace utilization?
SQL>select
a.tablespace_name,
round(a.bytes_alloc / 1024 / 1024) megs_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
round(maxbytes/1048576) Max
from
( select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
( select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where
a.tablespace_name = b.tablespace_name (+)
union
all
select
h.tablespace_name,
round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576)
megs_free,
round(sum(nvl(p.bytes_used, 0))/ 1048576) megs_used,
round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
round(sum(f.maxbytes) / 1048576) max
from
sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f
where
p.file_id(+) = h.file_id
and
p.tablespace_name(+) = h.tablespace_name
and
f.file_id = h.file_id
and
f.tablespace_name = h.tablespace_name
group
by h.tablespace_name
ORDER
BY 1
/
TABLESPACE_NAME
|
MEGS_ALLOC
|
MEGS_FREE
|
MEGS_USED
|
PCT_FREE
|
PCT_USED
|
MAX
|
SYSAUX
|
325
|
115
|
210
|
35
|
65
|
325
|
SYSTEM
|
450
|
167
|
283
|
37
|
63
|
450
|
TEMPTS1
|
65
|
65
|
0
|
100
|
0
|
0
|
UNDOTBS
|
450
|
439
|
11
|
97
|
3
|
32768
|
USERS
|
100
|
94
|
6
|
94
|
6
|
32768
|
Tablespace name
|
size allocated
|
free space
|
space used
|
% free space
|
% space used
|
