DBA Tablespace Usage

Description

Categories: Enginatics
Repository: Github

Tablespace usage including currently active undo and temp tablepace usage in Megabytes

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Copy

select
dt.contents,
dtum.tablespace_name,
vp.value*dtum.used_space/1000000 data_size,
ddf.bytes/1000000 file_size,
(ddf.bytes-vp.value*dtum.used_space)/1000000 unused_file_size,
vp.value*dtum.used_space/ddf.bytes*100 file_used_percent,
vp.value*dtum.tablespace_size/1000000 max_extensible_size,
dtum.used_percent max_used_percent,
nvl(due.active,(select vp.value*vss.used_blocks/1000000 from v$sort_segment vss where dt.tablespace_name=vss.tablespace_name)) active,
due.unexpired_undo,
due.expired_undo
from
dba_tablespaces dt,
(select vp.value from v$parameter vp where vp.name like 'db_block_size') vp,
dba_tablespace_usage_metrics dtum,
(
select distinct ddf.tablespace_name, sum(ddf.bytes) over (partition by ddf.tablespace_name) bytes from dba_data_files ddf union all
select distinct dtf.tablespace_name, sum(dtf.bytes) over (partition by dtf.tablespace_name) bytes from dba_temp_files dtf
) ddf,
(
select
*
from
(select distinct due.tablespace_name, due.status, sum(due.blocks) over (partition by due.tablespace_name,due.status)*8192/1000000 space from dba_undo_extents due) due
pivot (
sum(due.space)
for status in (
'ACTIVE' active,
'UNEXPIRED' unexpired_undo,
'EXPIRED' expired_undo
)
)
) due
where
dt.tablespace_name=dtum.tablespace_name and
dt.tablespace_name=ddf.tablespace_name(+) and
dt.tablespace_name=due.tablespace_name(+)
order by
dt.contents,
dtum.used_space desc