APPS_TS_TX_IDX growth rapidly.

[ErmanArslansOracleBlog]

Please check the size of that lob(SYS_LOB0001144624C00040$$) using the following queries;

Check the bytes
select bytes from dba_segments where segment_name ='<lob segment name>' and owner ='';

Check the column name
SELECT TABLE_NAME, COLUMN_NAME  FROM DBA_LOBS WHERE OWNER = '<owner>' AND  SEGMENT_NAME= '<lob segment name>' ;

Check the size
select sum(dbms_lob.getlength (<lob column name>)) from <table_name>;

[raiq1]

dear sir,

check the output for your below queries.


SQL> select bytes from dba_segments where segment_name ='SYS_LOB0001144624C00040$$' and owner ='APPLSYS';

     BYTES
----------
  55312384


SQL> SELECT TABLE_NAME, COLUMN_NAME  FROM DBA_LOBS WHERE OWNER = 'APPLSYS' AND  SEGMENT_NAME='SYS_LOB0001144624C00040$$'
  2  ;

TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
WF_NOTIFICATION_OUT
"USER_DATA"."TEXT_LOB"


lAST QUERY are given error, please check below & rectify, above 2 queries output are there.

select sum(DBMS_LOB.getlength("USER_DATA"."TEXT_LOB")) FROM APPLSYS.WF_NOTIFICATION_OUT

ERROR at line 1:
ORA-00904: "USER_DATA"."TEXT_LOB": invalid identifier

[ErmanArslansOracleBlog]

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

[raiq1]

Dear sir,

Thanks for query modification , i ran it today and output are attached.

Kindly check & advice.

Regards,
user_data_09march.xls

[ErmanArslansOracleBlog]

What time interval did you query? You have to query the time period when there is sudden tablespace growth.

As you see in the uploaded output, there is no big increase in the size of APPS_TS_TX_IDX or APPS_TS_TX_DATA...
So probably, you queried the wrong time interval. (remember -> you said ; you increased the tablespace size for APPS_TS_TX_DATA or IDX and then you saw a big increase there and you ended up with the full APPS_TS_TX_DATA or IDX tablespaces again. So you need to query that specific time interval to understand what object/objects are responsible for this growth.

Please revise the time condition and query again.

[raiq1]

ok sir, i will wait till the abnormal growth again in APPS_TS_TX_DATA & APPS_TX_TX_IDX tablespace.

i will update the post again after find abnormal growth .


Any how, thanks alot for supporting .


Regards,

[raiq1]

Dear Erman sir,

for past 1 week, i have monitored the growth of both tablespaces APPS_TS_TX_DATA & APPS_TS_TX_IDX

yesterday have a spike of both tablespaces.


both tablespaces growth are 250 mb around in one day.

Is it normal tablespaces growth or little high, kindly confirm me.

Regards,

[ErmanArslansOracleBlog]

That may be normal. But! you need to ask this question to your superusers, your functional admins..
These things are proportional to the work that is done on EBS..

[raiq1]

Dear Erman sir,

after continue monitor the system, today also tablespace growth are spike and increase rapidly.

APPS_TS_TX_DATA  increase  1227 mb in one day

APPS_TS_TX_IDX increase 854 mb in one day.

Kindly guide us which segment are increase in both tablespaces, need to know actual table/index growth

Regards,

[ErmanArslansOracleBlog]

Your growth is around 1 GB. This may be normal..
Also, I already provided you the guidance for checking the object growth.. See my earlier updates.