DBA Segments

➜ Back to Library
Description
Categories: Enginatics, Kcapps
Repository: Github
Database segments such as tables, indexes, lob segments by size and total database size summary
Run DBA Segments and other Oracle EBS reports with Blitz Report™ on our demo environment
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
from
dba_segments ds,
(select di.owner, di.index_name, di.table_owner, di.table_name from dba_indexes di where '&enable_table'='Y') di,
(
select
dl.segment_name,
nvl(di.table_owner,dl.owner) table_owner,
nvl(di.table_name,dl.table_name) table_name
from
dba_lobs dl,
dba_secondary_objects dso,
dba_indexes di
where
'&enable_table'='Y' and
dl.owner=dso.secondary_object_owner(+) and
dl.table_name=dso.secondary_object_name(+) and
dso.index_owner=di.owner(+) and
dso.index_name=di.index_name(+)
) dl
where
1=1 and
case when ds.segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION') then ds.owner end=di.owner(+) and
case when ds.segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION') then ds.segment_name end=di.index_name(+) and
case when ds.segment_type in ('LOBSEGMENT','LOB PARTITION','LOB SUBPARTITION') then ds.segment_name end=dl.segment_name(+)
) x
&where2
order by
x.bytes desc
Parameter Name SQL text Validation
Group by 
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
Table Name 
:table_name=:table_name
 LOV
Tablespace Name 
ds.tablespace_name=:tablespace_name
 LOV
Owner 
ds.owner=:owner
 LOV
Download
Related Blitz Reports
DBA SGA Buffer Cache Object Usage DBA Dependencies (uses) DIS End User Layers DBA Dependencies (used by) DBA Result Cache Objects and Invalidations DBA AWR Blocking Session Summary DBA AWR Active Session History DBA SGA Active Session History DBA Blocking Sessions
Blitz Report™

Blitz Report™ provides multiple benefits: