88 lines
2.6 KiB
SQL
88 lines
2.6 KiB
SQL
-- for the current top 10 segments (by size) list the history of the space used / allocated delta per day for the past month.
|
|
-- as dba_hist_seg_stat does not contain all segments, but only the ones that oracle determines to to be interesting, the history may be lacking or incomplete for some of the segments
|
|
-- also, default the history in dba_hist_seg_stat is only kept for 7 days
|
|
|
|
column owner format a30 heading "Owner"
|
|
column segment_name format a30 heading "Segment"
|
|
column segment_type format a18 heading "Type"
|
|
column tablespace_name format a30 heading "Tablespace"
|
|
column begin_interval_day_s format a10 heading "Day"
|
|
column current_size_mb format 999G999G990D99 heading "Curr Size (MB)"
|
|
column space_used_delta_mb format 999G999G990D99 heading "Space Used Delta (MB)"
|
|
column space_allocated_delta_mb format 999G999G990D99 heading "Space Alloc Delta (MB)"
|
|
|
|
break on owner on segment_name skip 1 on segment_type on tablespace_name on current_size_mb
|
|
|
|
with ctop
|
|
as
|
|
( select
|
|
*
|
|
from
|
|
( select
|
|
owner,
|
|
segment_name,
|
|
partition_name,
|
|
segment_type,
|
|
tablespace_name,
|
|
bytes
|
|
from
|
|
dba_segments
|
|
where
|
|
segment_type not in ('ROLLBACK', 'TEMPORARY', 'TYPE2 UNDO')
|
|
order by
|
|
bytes desc
|
|
)
|
|
where
|
|
rownum <= 10
|
|
),
|
|
segstat
|
|
as
|
|
( select
|
|
trunc(snap.begin_interval_time) begin_interval_day,
|
|
obj.owner,
|
|
obj.object_name,
|
|
obj.subobject_name,
|
|
sum(stat.space_used_delta) space_used_delta,
|
|
sum(stat.space_allocated_delta) space_allocated_delta
|
|
from
|
|
dba_hist_snapshot snap,
|
|
dba_hist_seg_stat stat,
|
|
dba_hist_seg_stat_obj obj
|
|
where
|
|
snap.dbid = stat.dbid
|
|
and snap.snap_id = stat.snap_id
|
|
and snap.instance_number = stat.instance_number
|
|
and stat.dbid = obj.dbid
|
|
and stat.obj# = obj.obj#
|
|
and snap.begin_interval_time >= add_months(trunc(sysdate), -1)
|
|
group by
|
|
trunc(snap.begin_interval_time),
|
|
obj.owner,
|
|
obj.object_name,
|
|
obj.subobject_name
|
|
)
|
|
select
|
|
ctop.owner,
|
|
ctop.segment_name,
|
|
ctop.segment_type,
|
|
ctop.tablespace_name,
|
|
ctop.bytes/1024/1024 current_size_mb,
|
|
to_char(segstat.begin_interval_day, 'DD/MM/YYYY') begin_interval_day_s,
|
|
segstat.space_used_delta/1024/1024 space_used_delta_mb,
|
|
segstat.space_allocated_delta/1024/1024 space_allocated_delta_mb
|
|
from
|
|
ctop,
|
|
segstat
|
|
where
|
|
ctop.segment_name = segstat.object_name(+)
|
|
and nvl(ctop.partition_name, 'x') = nvl(segstat.subobject_name,'x')
|
|
order by
|
|
ctop.bytes desc,
|
|
ctop.owner,
|
|
ctop.segment_name,
|
|
ctop.partition_name,
|
|
segstat.begin_interval_day
|
|
;
|
|
|
|
clear breaks
|