OIM 11G Orchestration tables

Orchestration is vital component in OIM and we can say it is heart of OIM because, all operations, such as create user, modify user, delete, ldap sync, etc., were closely integrate with OIM Orchestration.

Known Issue: OIM Orchestration will retry failed event handlers ONLY 2 times and will ignore after that. Because, the retry limit was hard coded in OIM.

Table Name	Table Description
ORCHPROCESS	Stores the process instances that are being executed.
ORCHEVENTS	Stores event handler names, status and result for all orchestration processes. Event status like COMPLETED, FAILED, PENDING, etc.
ORCHFAILEDEVENTS	Stores event handler information that are executed because of failures in main flow.

SQL Query:

Below sql query is to get list of event handlers, which are executed for a particular users during enable process:

This query used to get user key from usr table.

select usr_key from USR 
where usr_login = ‘chellappan.sampath’;
-- 1024

This query get process instance of enabled user ‘chellappan.sampath’

select id from orchprocess 
where entityid=’1024’ and entitytype='User' and operation='ENABLE';
-- 561092

This query gets all the event handler for enabled user ‘chellappan.sampath’

select * from orchevents 
where processid=’561092’ order by orchorder;

Similarly you can do for user enable, create user, etc.,