select
tablespace_name,
max,
alloc,
used,
round(100 * used / alloc, 2) pct_used_alloc,
round(100 * used / max, 2) pct_used_max,
status,
logging,
force_logging,
extent_management,
allocation_type
from
(
select
t.status status,
t.logging logging,
t.force_logging force_logging,
t.extent_management extent_management,
t.allocation_type allocation_type,
a.tablespace_name tablespace_name,
round(a.bytes_alloc , 2) alloc,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) , 2) used,
round(a.maxbytes , 2) max
from
dba_tablespaces t,
(
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(+)
and a.tablespace_name = t.tablespace_name
union all
select
t.status status,
t.logging logging,
t.force_logging force_logging,
t.extent_management extent_management,
t.allocation_type allocation_type,
h.tablespace_name tablespace_name,
round(sum(nvl(f.bytes, 0)) , 2) alloc,
round(sum(nvl(p.bytes_used, 0)) , 2) used,
round(sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) , 2) max
from
dba_tablespaces t,
v$temp_space_header h,
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
and t.tablespace_name = h.tablespace_name
group by
h.tablespace_name,
t.status,
t.logging,
t.force_logging,
t.extent_management,
t.allocation_type )
order by
4
TABLESPACE_NAME | MAX | ALLOC | USED | PCT_USED_ALLOC | PCT_USED_MAX | STATUS | LOGGING | FORCE_LOGGING | EXTENT_MANAGEMENT | ALLOCATION_TYPE |
---|---|---|---|---|---|---|---|---|---|---|
TEMP | 34,359,721,984 | 1,342,177,280 | 1,048,576 | 0.08 | 0 | ONLINE | NOLOGGING | NO | LOCAL | UNIFORM |
USERS | 34,359,721,984 | 5,242,880 | 1,376,256 | 26.25 | 0 | ONLINE | LOGGING | NO | LOCAL | SYSTEM |
TESTTBS | 68,719,443,968 | 209,715,200 | 2,097,152 | 1 | 0 | ONLINE | LOGGING | NO | LOCAL | SYSTEM |
UNDOTBS1 | 34,359,721,984 | 94,371,840 | 21,626,880 | 22.92 | 0.06 | ONLINE | LOGGING | NO | LOCAL | SYSTEM |
SYSAUX | 34,359,721,984 | 587,202,560 | 553,451,520 | 94.25 | 1.61 | ONLINE | LOGGING | NO | LOCAL | SYSTEM |
SYSTEM | 34,359,721,984 | 786,432,000 | 779,747,328 | 99.15 | 2.27 | ONLINE | LOGGING | NO | LOCAL | SYSTEM |