Re: APPS_TS_TX_IDX growth rapidly.

Posted by raiq1 on Feb 27, 2021; 7:21pm
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/APPS-TS-TX-IDX-growth-rapidly-tp9397p9410.html

Dear Ermaan,

Kindly check the below query, is it fullfil our requirement. Make sure i have oracle 11.2.0.4 db with 12.1.3 ebs.

please check & confirm me.


select
so.owner,
so.object_name,
--so.subobject_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 so.object_type not like 'LOB%'
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 > trunc(sysdate) - &DAYS_BACK
)
group by
so.owner,
so.object_name,
--so.subobject_name,
so.object_type,
so.tablespace_name
order by 5 desc
fetch first &TOP rows only