1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
set tab off
set linesize 200
set pagesize 100
select
files.tablespace_name,
maxbytes as "BYTES SIZE",
bytes - free_bytes as "BYTES USED",
maxbytes - (bytes - free_bytes) as "BYTES AVAIL",
round(maxbytes / 1048576, 2) as "MB SIZE",
round((bytes - free_bytes) / 1048576, 2) as "MB USED",
round((maxbytes - (bytes - free_bytes)) / 1048576, 2) as "MB AVAIL",
round(((bytes - free_bytes) / maxbytes) * 100, 2) as "% USED",
round((100 - ((bytes - free_bytes) / maxbytes) * 100), 2) as "% FREE"
from
(select tablespace_name, sum(bytes) as bytes, sum(maxbytes) as maxbytes from
(select tablespace_name, bytes, case autoextensible when 'YES' then maxbytes else bytes end as maxbytes from dba_data_files) group by tablespace_name) files,
(select tablespace_name, sum(bytes) as free_bytes from dba_free_space group by tablespace_name) free
where files.tablespace_name = free.tablespace_name
order by 9;
order by free.tablespace_name;
|