select ts_name
, extensible_bytes extensible
, allocated_bytes allocated
, alloc_free
, allocated_bytes - alloc_free used
, 100 * (allocated_bytes - alloc_free) / allocated_bytes pct_used
, to_number(decode(allocated_bytes, extensible_bytes, NULL,
extensible_bytes
- (allocated_bytes - alloc_free))) ne
, to_number(decode(allocated_bytes, extensible_bytes, NULL,
100 * (extensible_bytes - (extensible_bytes - (allocated_bytes - alloc_free)))
/ extensible_bytes)) pct_used_ext
from (
select a.tablespace_name ts_name
, sum(decode(b.autoextensible, 'YES', b.maxbytes, b.bytes))
/ count(distinct a.file_id||'.'||a.block_id) extensible_bytes
, sum(b.bytes)/count(distinct a.file_id||'.'||a.block_id) allocated_bytes
, sum(a.bytes)/count(distinct b.file_id) alloc_free
from sys.dba_free_space a
, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name (+)
group by a.tablespace_name
, b.tablespace_name)
order by 6 desc;
TS_NAME | EXTENSIBLE | ALLOCATED | ALLOC_FREE | USED | PCT_USED | NE | PCT_USED_EXT |
---|---|---|---|---|---|---|---|
SYSTEM | 34,359,721,984 | 786,432,000 | 6,684,672 | 779,747,328 | 99.15 | 33,579,974,656 | 2.2693644854 |
SYSAUX | 34,359,721,984 | 587,202,560 | 37,617,664 | 549,584,896 | 93.59375 | 33,810,137,088 | 1.5995033262 |
USERS | 34,359,721,984 | 5,242,880 | 3,866,624 | 1,376,256 | 26.25 | 34,358,345,728 | 0.004005434 |
UNDOTBS1 | 34,359,721,984 | 94,371,840 | 73,465,856 | 20,905,984 | 22.1527777778 | 34,338,816,000 | 0.0608444504 |
TESTTBS | 68,719,443,968 | 209,715,200 | 207,618,048 | 2,097,152 | 1 | 68,717,346,816 | 0.0030517593 |