create or replace view v_undo_snap_short as
with s as (select * from table(pkg_undo_snap.show_snaps)),
m as (select * from table(pkg_undo_snap.show_mystat_snaps)),
r as (select * from table(pkg_undo_snap.show_rollstat_snaps)),
e as (select * from table(pkg_undo_snap.show_undo_extents_snaps)),
es as
(
select snap_no,
usn,
sum(decode(status,'ACTIVE',1)) A,
sum(decode(status,'UNEXPIRED',1)) U,
sum(decode(status,'EXPIRED',1)) E
from e
group by snap_no, usn
),
ec as
(
select snap_no, stragg(extschg) extschg
from
(
select nvl(e.snap_no, ep.snap_no + 1) snap_no,
'['||nvl(e.block_id, ep.block_id)||': '||
ep.usn || '(' || nvl(substr(ep.status,1,1), 'F') || ') -> ' ||
e.usn || '(' || nvl(substr(e.status ,1,1), 'F') || ')]' extschg
from e full join e ep on ep.snap_no = e.snap_no - 1 and ep.block_id = e.block_id
where (decode(ep.usn, e.usn, 1) is null or decode(ep.status, e.status, 1) is null)
and nvl(ep.snap_no, 0) < (select max(s.snap_no) from s)
and nvl(e.snap_no, 2) > 1
order by nvl(e.snap_no, ep.snap_no + 1), nvl(e.block_id, ep.block_id)
)
group by snap_no
)
select decode(s.trgcnt-lag(s.trgcnt)over(order by s.snap_no),null,'',0,'',1,'.',2,'*','**') f,
s.snap_no sn,
s.user_no un,
s.usn,
m.undo_cvs-lag(m.undo_cvs)over(order by s.snap_no) undo,
s.trgcnt-lag(s.trgcnt)over(order by s.snap_no) t,
r.extents ec,
r.curext ce,
r.curblk cb,
nullif(r.extents-rp.extents, 0) d,
nullif(r.extends-rp.extends, 0) x,
nullif(r.shrinks-rp.shrinks, 0) s,
es.A,
es.U,
es.E,
s.used_undo uu,
e.block_id bid,
ec.extschg
from s
join m on m.snap_no = s.snap_no
left join r on r.snap_no = s.snap_no and r.usn = s.usn
left join r rp on rp.snap_no = s.snap_no - 1 and rp.usn = s.usn
left join e on e.snap_no = s.snap_no and e.usn = s.usn and e.extent_id = r.curext
left join es on es.snap_no = s.snap_no and es.usn = s.usn
left join ec on ec.snap_no = s.snap_no; |