How to Check OIM Schema Version

To find out the your schema version, execute the following query:

select * from schema_version_registry;

Oracle DB: Get All the Foriegn Keys References on a Specific Column

At times it is useful to know all the foreign keys of a specific table column. The query provided here will give you the table name that has the foreign key, the name of the foreign key constraint, the table and column the foreign key reference to. Change the value for ucc.column_name to the column name you want all the foreign key references of.

SELECT
   uc.table_name as foreign_table,
   uc.constraint_name as foreign_constaint_name,
   ucc.table_name,
   uc.r_constraint_name,
   ucc.column_name
FROM
   user_constraints uc,
   user_cons_columns ucc
WHERE
   uc.r_constraint_name = ucc.constraint_name AND
   uc.constraint_type = 'R' AND
   ucc.column_name = 'USR_KEY';

SQL Query to get User Accounts Statuses

Version: Oracle Identity Manager 11.1.1.5.0
Description: Given here is a SQL query to get the statuses of each user's accounts (resource object instances).

Updating OIM Process Form of a Resource Object Instance Through SQL

Version: Oracle Identity Manager 11.1.1.5.0
Description: Given here is an SQL query to update every outdated Resource Object Instance's process form. A sample usage scenario is when you have users who have been provisioned to a target system (E.g. Active Directory, OpenLDAP) and the target accounts are already replicated in OIM. These target accounts are represented as Resource Object Instances in OIM , and the process form holds the data. Later you decide to add a new attribute on to the process form of a specific resource object and update it to the latest version through design console. You'll notice that on existing resource object instances process forms the new attribute does not appear. To fix that, execute the query given here. You can also use OIM's Form Version Control Utility to the same.