Forum Stats

  • 3,816,002 Users
  • 2,259,128 Discussions
  • 7,893,362 Comments

Discussions

Table ATTACHMENT on soa 11.1.1.6.0

3056701
3056701 Member Posts: 1
edited Nov 23, 2016 4:58AM in SOA Suite Discusssions

Hello, I am having issue to manage the growth of ATTACHMENT TABLE on SOA SUITE METADATA Version 11.1.1.6.0.

The purge_soa with delete_instances procedore did'nt work on it.

Is it possible that this version have this bug? BUG:18835052

Btw, I already tried to run the purge with all composites in Audit Development and did not work.

I already tried this script below, but did not work too.

set echo on

set verify on

set timing on

-- delete all instances older then 3 days

define days=3

alter session set nls_date_format='yyyymmdd hh24mi';

variable cur_datetime varchar2(13)

exec select (sysdate - &days) into :cur_datetime from dual;

prompt Purging data until:

print cur_datetime

delete from attachment c where exists (select a.key from attachment_ref a where a.key = c.key and exists (select b.ecid from composite_instance b where b.ecid = a.ecid and b.created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi')));

delete from attachment_ref a where exists (select b.ecid from composite_instance b where b.ecid = a.ecid and b.created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'));

alter table attachment enable row movement;

alter table attachment shrink space;

alter table attachment disable row movement;

alter table attachment_ref enable row movement;

alter table attachment_ref shrink space;

alter table attachment_ref disable row movement;

Someone can help me with this, please?

Tagged:

Answers

  • Srinivas Y
    Srinivas Y Member Posts: 88
    edited Nov 23, 2016 4:58AM

    Hello

    Can you try running this subquery

    select b.ecid from composite_instance b where b.ecid = a.ecid and b.created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi')

    I doubt the necessary would have already been purged when you run the normal purge script and since the subquery result is empty you see no difference in atttachment or attachment_ref table.

    Try going with a hard delete i.e. non conditional purge or truncate the tables and then do a shrink space.

    From next run on include the above mentioned statements before the data in composite_instance is purged and see if it makes a difference

    Regards

    Srini

This discussion has been closed.