Manually Cleaning / Archiving Orchestration Tables

Due to the high volume of data accumulated in ORCHESTRATION tables,the current purge
mechanism is non-conventional and is based on the Table Swap approach.


1. Login into OIM Schema using SQLPLUS or using any IDE like SQL Developer

Command for SQLPLUS
sqlplus <oim_schema>/<oim_pwd>@<tnsname>

-- From OIM DEV Schema--

2. Run the following query:

SQL> SELECT * FROM user_objects WHERE status!='VALID';

If there is any row, capture the output in an excel sheet.

-- The below steps are for illustration purpose, and will purge data from Orchestration table for all status (apart from 'ACTIVE' status within creation date).
-- For purging data for the status as per customer requirement(s), please make appropriate changes to the below steps.

3. -- Check how many ACTIVE orchestration and child footprints are present and capture the counts.

SQL> SELECT COUNT(1) FROM orchprocess WHERE upper(status) IN ('ACTIVE') AND  CREATEDON  >  TO_DATE('17-NOV-2017', 'DD-MON-YYYY');

SQL> SELECT COUNT(1) FROM orchevents  oe WHERE EXISTS (SELECT 1 FROM orchprocess op WHERE upper(op.status) IN ('ACTIVE') AND  op.CREATEDON  >  TO_DATE('17-NOV-2017', 'DD-MON-YYYY') AND oe.processid=op.id);

SQL> SELECT COUNT(1) FROM orchfailedevents  ofe WHERE EXISTS (SELECT 1 FROM orchprocess op WHERE upper(op.status) IN ('ACTIVE') AND op.CREATEDON  >  TO_DATE('17-NOV-2017', 'DD-MON-YYYY') AND ofe.processid=op.id);

SQL> SELECT COUNT(1) FROM callback_invocation_result  cir WHERE EXISTS (SELECT 1 FROM orchprocess op WHERE upper(op.status) IN ('ACTIVE')  AND op.CREATEDON  >  TO_DATE('17-NOV-2017', 'DD-MON-YYYY') AND cir.orchestration_id=op.id);

SQL> SELECT COUNT(1) FROM aud_jms;


4. -- Create Backup Table from Original table using the following SQLs

SQL> CREATE TABLE orchprocess_bkp AS SELECT * FROM orchprocess WHERE upper(status) IN ('ACTIVE') AND  CREATEDON  >  TO_DATE('17-NOV-2017', 'DD-MON-YYYY');


SQL> CREATE UNIQUE INDEX udx_orchproc_bkp ON orchprocess_bkp(id);


SQL> CREATE TABLE orchevents_bkp AS SELECT * FROM orchevents oe WHERE EXISTS (SELECT 1 FROM orchprocess_bkp ob WHERE ob.id=oe.processid);


SQL> CREATE TABLE orchfailedevents_bkp AS SELECT * FROM orchfailedevents ofe WHERE EXISTS (SELECT 1 FROM orchprocess_bkp ob WHERE ob.id=ofe.processid);


SQL> CREATE TABLE callback_invocation_result_bk AS SELECT * FROM callback_invocation_result cir WHERE EXISTS (SELECT 1 FROM orchprocess_bkp ob WHERE ob.id=cir.orchestration_id);


SQL> DROP INDEX udx_orchproc_bkp;


5. -- Check the backup table count using following SQLs. Match this with the count got in Step#3

SQL> SELECT COUNT(1) FROM orchprocess_bkp;

SQL> SELECT COUNT(1) FROM orchevents_bkp;

SQL> SELECT COUNT(1) FROM orchfailedevents_bkp;

SQL> SELECT COUNT(1) FROM callback_invocation_result_bk;


6. -- Rename the original tables constraints using following SQLs

These are the OTB constraints. If there is any other custom constraints, then those also need to be renamed.

List of constrains can be found from following query:

(i)  SQL> SELECT constraint_name,table_name FROM user_constraints WHERE table_name IN ('ORCHPROCESS','ORCHEVENTS','ORCHFAILEDEVENTS','CALLBACK_INVOCATION_RESULT') AND constraint_type IN ('P','R','U');

(ii) SQL> SELECT constraint_name,table_name,column_name,position FROM user_cons_columns WHERE constraint_name IN (SELECT constraint_name FROM user_constraints WHERE table_name IN ('ORCHPROCESS','ORCHEVENTS','ORCHFAILEDEVENTS','CALLBACK_INVOCATION_RESULT') AND constraint_type IN ('P','R','U'))
      ORDER BY table_name,constraint_name,position;

Its worth to mention that the constraint name length should not exceed 30 characters as this is the maximum length supported by Oracle RDBMS.

SQL> ALTER TABLE orchprocess RENAME CONSTRAINT orchprocess_pk TO orchprocess_pk_bkp;

SQL> ALTER TABLE orchevents RENAME CONSTRAINT orchevents_pk TO orchevents_pk_bkp;

SQL> ALTER TABLE orchfailedevents RENAME CONSTRAINT orchfailedevents_pk TO orchfailedevents_pk_bkp;

SQL> ALTER TABLE callback_invocation_result RENAME CONSTRAINT pk_callback_invocation_result TO pk_calbak_invoc_result_bkp;

SQL> ALTER TABLE callback_invocation_result RENAME CONSTRAINT fk_orchestration_id TO fk_orchestration_id_bkp; 


7. -- Rename Indexes of original tables using following SQLs

These are the OTB indexes. If there is any other custom indexes created, then those also need to be renamed.

List of indexes can be found from following query:

(i)  SQL> SELECT index_name,table_name FROM user_indexes WHERE table_name IN ('ORCHPROCESS','ORCHEVENTS','ORCHFAILEDEVENTS','CALLBACK_INVOCATION_RESULT') AND index_type!='LOB';

(ii) SQL> SELECT index_name,table_name,column_name,column_position FROM user_ind_columns WHERE index_name IN (SELECT index_name FROM user_indexes WHERE table_name IN ('ORCHPROCESS','ORCHEVENTS','ORCHFAILEDEVENTS','CALLBACK_INVOCATION_RESULT') AND index_type!='LOB') ORDER BY table_name,index_name,column_position;

Its worth to mention that the index name length should not exceed 30 characters as this is the maximum length supported by Oracle RDBMS.

SQL> ALTER INDEX orchprocess_pk RENAME TO orchprocess_pk_i1;

SQL> ALTER INDEX orchprocess_lob_idx RENAME TO orchprocess_lob_idx_i1;

SQL> ALTER INDEX idx_orchprocess_status RENAME TO idx_orchprocess_status_i1;

SQL> ALTER INDEX idx_orchprocess_ppid RENAME TO idx_orchprocess_ppid_i1;

SQL> ALTER INDEX idx_orchprocess_depprocessid RENAME TO idx_orchprocess_depprocid_i1;

SQL> ALTER INDEX orchevents_pk RENAME  TO orchevents_pk_i1;

SQL> ALTER INDEX idx_orchevents_processid RENAME  TO idx_orchevents_processid_i1;

SQL> ALTER INDEX orchfailedevents_pk RENAME TO orchfailedevents_pk_i1;

SQL> ALTER INDEX idx_orchfailedevents_processid RENAME TO idx_orchfailevnts_procid_i1;

SQL> ALTER INDEX pk_callback_invocation_result RENAME TO pk_calbak_invoc_result_i1;


8. -- Rename the original tables using following SQLs

SQL> ALTER TABLE orchprocess RENAME TO orchprocess_old;

SQL> ALTER TABLE orchevents RENAME TO orchevents_old;

SQL> ALTER TABLE orchfailedevents RENAME TO orchfailedevents_old;

SQL> ALTER TABLE callback_invocation_result RENAME TO callback_invocation_result_old;


9. -- Rename the backup table to original tables using following SQLs


SQL> ALTER TABLE orchprocess_bkp RENAME TO orchprocess;

SQL> ALTER TABLE orchevents_bkp RENAME TO orchevents;

SQL> ALTER TABLE orchfailedevents_bkp RENAME TO orchfailedevents;

SQL> ALTER TABLE callback_invocation_result_bk RENAME TO callback_invocation_result;

10. -- Add constraints to the newly created table using following SQLs

These are the OTB constraints. If there is any other custom constraints, then those also need to be created.

List of constrains can be found from step#6, query#(ii)

SQL> ALTER TABLE orchprocess ADD CONSTRAINT orchprocess_pk PRIMARY KEY (id);

SQL> ALTER TABLE orchevents ADD CONSTRAINT orchevents_pk PRIMARY KEY (id);

SQL> ALTER TABLE orchfailedevents ADD CONSTRAINT orchfailedevents_pk PRIMARY KEY (id);

SQL> ALTER TABLE callback_invocation_result ADD CONSTRAINT pk_callback_invocation_result PRIMARY KEY (correlation_id);

SQL> ALTER TABLE callback_invocation_result ADD CONSTRAINT fk_orchestration_id FOREIGN KEY (orchestration_id) REFERENCES orchprocess(id);


11. -- Add indexes to the newly created table using following SQLs

These are the OTB indexes. If there is any other custom indexes, then those also need to be created.

List of indexes can be found from step#7, query#(ii)

SQL> CREATE INDEX idx_orchprocess_depprocessid ON orchprocess(depprocessid);

SQL> CREATE INDEX idx_orchprocess_ppid ON orchprocess(parentprocessid);

SQL> CREATE INDEX idx_orchprocess_status ON orchprocess(status);

SQL> CREATE INDEX idx_orchevents_processid ON orchevents(processid);

SQL> CREATE INDEX idx_orchfailedevents_processid ON orchfailedevents(processid);


12. -- Run Compiler to recompile all objects

Login into SYS schema using SQLPLUS or using any IDE like SQL Developer

Command from SQLPLUS:
sqlplus sys/<sys_password>@<tnsname> AS SYSDBA

FROM SYS SCHEMA execute the following script 5-6 times:

SQL> EXEC UTL_RECOMP.recomp_serial('<oim_schema_name>');


13. -- Again login using OIM DEV schema and run the followng query

SQL> SELECT * FROM user_objects WHERE status!='VALID';

If there is any row, store the output in an excel sheet..
Compare this result with the Step#1 result. It should be less than the records received in Step#2. Else report to us.


14. -- Resume OIM application and keep monitoring the Application / OIM Logs.

If everything is working fine, (i.e. post sanity), drop the OLD orchestration table using following SQLs.

Login using OIM DEV schema and run the followng query:

SQL> DROP TABLE callback_invocation_result_old CASCADE CONSTRAINTS PURGE;

SQL> DROP TABLE orchfailedevents_old CASCADE CONSTRAINTS PURGE;

SQL> DROP TABLE orchevents_old CASCADE CONSTRAINTS PURGE;

SQL> DROP TABLE orchprocess_old CASCADE CONSTRAINTS PURGE;