OTM 6.2.10 purging of unwanted transmission data

 We perfomred and saved good amount of space by purging unwanted data on otm by following standard purge methods as per oracle  .Below are some of details .Activity needs downtime during index rebuild .

Biggest gain will be speed .

1.       Using GLOGOWNER/ user of database execute below command,

set timi on time on

set serveroutput on

                exec domainman.transmission_purge(265,4000,1000);

                Date Difference: 01.01.2015 – 22.09.2015 = 265

Keep 265 days old data, activity will take max 4000 minutes and execute it for bunches of             1000 records

265 Days can vary depending on the data to be required in OTM as per client’s approval

2.       Execute below statement to release space on database level

set timi on time on

ALTER TABLE I_TRANSMISSION ENABLE ROW MOVEMENT;

ALTER TABLE I_TRANSMISSION MODIFY LOB (XML_BLOB) (SHRINK SPACE);

ALTER TABLE I_TRANSMISSION DISABLE ROW MOVEMENT;

                ALTER TABLE I_TRANSACTION ENABLE ROW MOVEMENT;

ALTER TABLE I_TRANSACTION MODIFY LOB (XML_BLOB) (SHRINK SPACE);

                ALTER TABLE I_TRANSACTION DISABLE ROW MOVEMENT;

After that we have to rebuild indexes to avoid performance issue, please see below attached list of indexes which need to be rebuild.