DBA Segments

Description
Categories: Enginatics, Toolkit - DBA
Repository: Github Columns: Tablespace Name, Owner, Segment Type, Segment Name, Percentage, Mb, Total Mb, Table Name, Num Rows, Last Analyzed ...
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
x.table_name=nvl(:table_name,x.table_name) and
x.table_owner=dt.owner(+) and
x.table_name=dt.table_name(+)