Re: APPS_TS_TX_IDX growth rapidly.

Posted by ErmanArslansOracleBlog on
URL: http://erman-arslan-s-oracle-forum.114.s1.nabble.com/APPS-TS-TX-IDX-growth-rapidly-tp9397p9456.html

It is weird.. That lob column is 53 mb in size.. I mean its current size is so small when compared the size reported in our ouput.. There we saw a huge increase in its size.
Maybe it is increased and decreased later, and its current size is that's why small..
or! our query is wrong..

Please modify the below query -> especially modify this line according to your needs ->  "end_interval_time between trunc(sysdate) - 2 and trunc(sysdate) -1 "
After modifying it to query a time interval where you see a big increase in tablespace size, "execute it.."
What is the output?

select
so.owner,
so.object_name,
so.object_type,
so.tablespace_name,
round(sum(ss.space_used_delta)/1024/1024) growth_mb
from
dba_hist_seg_stat ss,
dba_hist_seg_stat_obj so
where
ss.obj# = so.obj#
and ss.dataobj# = so.dataobj#
and so.owner != '** MISSING **' -- segments already gone
and so.object_name not like 'BIN$%' -- recycle-bin
and ss.snap_id > (
select min(sn.snap_id)
from dba_hist_snapshot sn
where
sn.dbid = (select dbid from v$database)
and sn.end_interval_time between trunc(sysdate) - 2 and trunc(sysdate) -1
)
group by
so.owner,
so.object_name,
so.object_type,
so.tablespace_name
order by 5 desc