SQL Query for OIM11gR2

List of Table and Description

Table Name	Table Description
OIU	Object Instance Request Target User Information. Associate user information to the resource object instance when provisioning take places.
OST	Object Status Information.
OBI	Object Instance Information. Once resource provisioned to user, OIM created resource instance for each resource provisioning.
OBJ	Resource Object definition information This contains detail about resource such as resource name, auto-save enable or not and auto-prepopulate is enable or not, and whether or not the resource object allows multiple instances.
USR	It contains user information like login id, password, etc.,
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.
UPA	User profile audit information
USG	Role assigned to user

Query to list the resource that are in different status for given user-

select oiu.oiu_key, oiu.obi_key, oiu.orc_key, ost.ost_status, obj.obj_name, obj.obj_key,oiu.req_key

from oiu

inner join ost on oiu.ost_key = ost.ost_key

inner join obi on oiu.obi_key = obi.obi_key

inner join obj on obi.obj_key = obj.obj_key

where oiu.usr_key =(select usr_key from usr where usr_login='TESTUSR01');

Changing Resource Status in OIM in Account Tab

Through below query we can change the resource (AD, LDAP, Exchange etc..) Status.

update oiu set ost_key=(select ost_key from ost where obj_key=(select obj_key from obj where obj.obj_name = 'LDAP User') and ost_status='Revoked') where oiu.orc_key in(

SELECT orc_key  FROM ud_ldap_usr WHERE  ud_ldap_usr_userid in ('TESTUSR01'));

OIM SQL query to force users to change password on next login

When user’s password reset by either OIM Admin or API, user will be prompt to reset on next login. 

We can avoid the force user password on next login by update column 'USR_CHANGE_PWD_AT_NEXT_LOGON' in table ‘usr’. This column takes values 0 or 1. 

The column value 0 means User not forced to reset password on next login.

update usr set USR_CHANGE_PWD_AT_NEXT_LOGON='0'

where usr_login = 'TESTUSR01';

The column value 1 means User forced to reset password on next login.

update usr set USR_CHANGE_PWD_AT_NEXT_LOGON='1'

where usr_login = ‘TESTUSR01’;

OIM 11G Orchestration Query-

Orchestration is main Component in OIM, Operations, such as create user, modify user, Delete, Enable 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.

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 = ‘TESTUSR01’;

-- 1065

This query get process instance of enabled user ‘TESTUSR01’

select id from orchprocess

where entityid=’1065’ and entitytype='User' and operation='ENABLE';

-- 367098

This query gets all the event handler for enabled user ‘TESTUSR01’

select * from orchevents

where processid=’367098’ order by orchorder;

In the same way we can use for Create, Modify, Delete, Disable...etc Operation.

OIM SQL query to find who modified user attributes

We can identify when and who made change for user profile attributes for example, email address.

Below query fetch the email address value for user ‘TESTUSR01’ from audit table: 

select field_name, field_old_value, field_new_value

from upa_fields fields

where upa_usr_key in ( select upa_key from upa

where upa_key in (select usr_key  from usr

where lower(usr_login) like 'TESTUSR01')))

and field_name = 'Users.Email'

order by upa_usr_key, field_name;

OIM SQL query to find who assigned role to users

OIM provides strong auditing features that will capture all user profile modification. It will be stored on UPA table.

Below query gets list of roles when was assigned to user ‘TESTUSR01’:

select * from upa

where usr_key = (select usr_key from usr

where lower(usr_login)= 'TESTUSR01')

and src like '%RoleManager%CREATE%';

Similarly, we can check for user role revoked by using src with ‘%RoleManager%DELETE%'

Oracle Schema Version Registry

Most of the Oracle Fusion Middleware components require existence of schemas in database prior to install. These schemas created and loaded using RCU. 

You can run query to get list of schema created though RCU:

select * from schema_version_registry;

OIM SQL query to get users whose specific role

 We often may need to find user who has specific role in OIM.

I have used query to get users who have role called ‘System Administrator’.

select usr.usr_display_name, usr.usr_login, usr.usr_email, ugp.ugp_name

from usg usg

left outer join usr usr on (usg.usr_key = usr.usr_key)

left outer join ugp ugp on (ugp.ugp_key = usg.ugp_key)

where upper(ugp_name) in (upper('System Administrator'));

Query to update the ldap common name on process form

update ud_ldap_usr set

UD_LDAP_USR_COMMON_NAME='Test01 User01'

where UD_LDAP_USR_USERID='TESTUSR01'