Reports 2017-11-18T12:27:27+00:00

DBA Segment Size

Description
Categories: DBA, Enginatics
Database segments such as tables, indexes, lob segments by size and total database size summary

select
&column2
100*x.bytes/x.total_bytes percentage,
x.bytes/1000000 mb,
x.total_bytes/1000000 total_mb
&column3
from
(
select distinct
&column1
sum(ds.bytes) over (partition by &partition_by) bytes,
sum(ds.bytes) over () total_bytes,
dt.num_rows,
dt.last_analyzed
from
dba_segments ds,
(select dt.* from dba_tables dt where '&enable_table'='Y') dt
where
1=1 and
case when ds.segment_type in ('TABLE','TABLE PARTITION') then ds.owner end=dt.owner(+) and
case when ds.segment_type in ('TABLE','TABLE PARTITION') then ds.segment_name end=dt.table_name(+)
) x
order by
x.bytes desc

Parameter Name SQL text Validation
Level of Detail
ds.tablespace_name,ds.owner,ds.segment_type,ds.segment_name
LOV
Segment Name like
ds.segment_name like upper(:segment_name)
LOV
Segment Type
ds.segment_type=:segment_type
LOV
Tablespace Name
ds.tablespace_name=:tablespace_name
LOV
Owner
ds.owner=:owner
LOV
Level of Detail
ds.tablespace_name,
Level of Detail
ds.owner,
Level of Detail
ds.segment_type,
Level of Detail
ds.tablespace_name,
ds.owner,
ds.segment_type,
ds.segment_name,
Level of Detail
x.tablespace_name,
x.owner,
x.segment_type,
x.segment_name,
Level of Detail
x.tablespace_name,
Level of Detail
x.owner,
Level of Detail
x.segment_type,
Level of Detail
,x.num_rows,
x.last_analyzed
Level of Detail
Y
Level of Detail
ds.segment_type
Level of Detail
ds.owner
Level of Detail
ds.tablespace_name