DBA Segments

Description
Categories: Enginatics, Toolkit - DBA
Repository: Github
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
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
Owner
ds.owner=:owner
LOV
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
Group by
ds.owner
Group by
ds.segment_type
Group by
ds.tablespace_name
Group by
ds.owner,
Group by
ds.tablespace_name,
ds.owner,
ds.segment_type,
ds.segment_name,
coalesce(case when ds.segment_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION') then ds.owner end,di.table_owner,dl.table_owner) table_owner,
coalesce(case when ds.segment_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION') then ds.segment_name end,di.table_name,dl.table_name) table_name,
Group by
ds.segment_type,
Group by
ds.tablespace_name,
Group by
x.owner,
Group by
x.tablespace_name,
x.owner,
x.segment_type,
x.segment_name,
Group by
x.segment_type,
Group by
x.tablespace_name,
Group by
,x.table_name,
dt.num_rows,
dt.last_analyzed
Group by
Y
Group by
,dba_tables dt
where
:dummy=:dummy and
x.table_name=nvl(:table_name,x.table_name) and
x.table_owner=dt.owner(+) and
x.table_name=dt.table_name(+)