OIM 11g / 9x: DB Table Description

To get some documentation on OIM Tables, try the following:
-----------------------------------------------------------
- Connect to OIM DB using Oracle SQL Developer.
- Left Panel -> List of Tables -> Click on table name. You will see table information on Right Panel
- Right Panel -> Detail Tab -> Look for Comments Field at the end. It has some documentation.

****************
Tables Analysis
****************

* SDC - User Defined Fields in User Form, etc...
- Used by interface - getFormFeildsData() to get user defined attributes.
Sample Query
------------
SELECT sdc.sdc_key, sdc.sdk_key, sdc_name, sdc_variant_type, sdc_sql_length, sdc_label, sdc_field_type, SDC_DEFAULT_VALUE, sdc_order, sdc_profile_enabled, sdc_encrypted, sdc_rowver,sdc_version, sdpv.sdp_property_value as Editable, sdpr.sdp_property_value as Optional, sdpv.sdp_property_value as Visible , sdplkv.sdp_property_value as LookupCode FROM sdk, sdc LEFT OUTER JOIN sdp_visible_v sdpv on sdc.sdc_key=sdpv.sdc_key LEFT OUTER JOIN sdp_required_v sdpr on sdc.sdc_key=sdpr.sdc_key LEFT OUTER JOIN sdp_lookupcode_v sdplkv on sdc.sdc_key=sdplkv.sdc_key WHERE sdc.sdk_key=sdk.sdk_key and (sdc.sdc_default is null or sdc.sdc_default='0') and sdc.sdc_version=0 and sdk.sdk_key=3 ORDER BY sdc_order asc, sdc.sdc_key asc;

Sample Result
--------------
155 3 USR_UDF_OBGUID String 300 ObjectGUID TextField 1 0 0000000000000001 0 false false
561 3 USR_UDF_MYCUSTATTR1BN String 25 MyCustAttr1 TextField CustAttr1DefValue 2 0 0000000000000000 0
562 3 USR_UDF_MYCUSTAATR2BN String 25 MyCustAttr2 TextField CustAttr2DefVal 3 0 0000000000000000 0

* ORC: Order Content Item Table
- Used by ScheduledTask to run a set of ordered events.

* SCH - Schedulted Item Table
- Used by tcScheduledTask to run scheduled Tasks.

* MIL : Tasks in Processes
- Contains all tasks from all processes.

* GCD : Generic Connector definitions Table.
- When you do "Install Connector" + "Create Generic Connector" in 11g UI, the entire information that you enter to create a new connector - like Format, data mapping between source and target, etc... will be formed into an XML document and will be stored in GCD_XML Field of GCD Table.
- During provisioning, a scheduled task will kick-in Generic Connector package - transform operation in OIM. This will transform data from USR tables' user record into target systems record using GCD_XML fields' connector definition.

* PTY - Property definition
- Properties Table
- Metadata used by OIM for System Properties defined. Product uses this to set status etc... as defined by OIM configuration
Sample Data
------------
70 XL.GTCAutoImport true GTC Auto Import 1 S 2 01-APR-11 1 01-APR-11 1 0000000000000000
71 XL.PagingSystemDefaultMaxRecords 1000 Paging System Default Max Records 1 S 2 01-APR-11 1 01-APR-11 1 0000000000000000
72 XL.SoDCheckRequired FALSE XL.SoDCheckRequired 1 S 2 01-APR-11 1 01-APR-11 1 000000000000
57 XL.RequestRaisedByYou.DayLimit 30 Property to indicate day limit set for Request raised by you 1 S 2 31-MAR-11 1 31-MAR-11 1 0000000000000000
58 XL.RequestRaisedForYou.DayLimit 30 Property to indicate day limit set for Request raised for you 1 S 2 31-MAR-11 1 31-MAR-11 1 0000000000000000

**************************************************
User Tables
**************************************************
* USR : All user information - very important table.
* UPH: User Policy Profile History Table

**************************************************
Resource Objects
**************************************************
Just like we have a class definition and a class instance in Java, we have Resource Object Definition and Resource Object Instance.
* OBJ : Resource Object Definition
- Defines structure of an object
Sample Data
------------
21 21 U Generic 1 1 Laptopres 0 1 1 0 0 0 05-APR-11 1 05-APR-11 1 0000000000000003 0 0
83 86 U Generic 1 1 Stapler 0 1 1 0 0 1 29-APR-11 1 29-APR-11 1 0000000000000001 0 0

* OBI : Resource Object Instance
- Entry for a resource object instantiated at run time.
- Very important as Provisioning operates on tcOBI to complete Provisioning.
Sample Data
------------
466 126 131 Data Received 1 27-JUN-11 1 27-JUN-11 1 0000000000000000

* RIU : Request Users Resolved Object Instances
- When you revoke a resource object from users resources, OIM will update revoke request information in this table
- Table Fields
RIU_KEY NUMBER, REQ_KEY NUMBER, OBJ_KEY NUMBER, USR_KEY NUMBER,
OIU_KEY NUMBER, OBI_KEY NUMBER, RIU_COMPLETED, RIU_DATA_LEVEL, RIU_CREATE
RIU_CREATEBY, RIU_UPDATE, RIU_UPDATEBY, RIU_NOTE, RIU_ROWVER
Sample Query Result
====================
1 110 125 182 235 444 1 27-JUN-11 1 27-JUN-11 1 0000000000000001
2 110 126 182 236 445 0 27-JUN-11 1 27-JUN-11 1 0000000000000000
3 111 125 183 237 448 1 27-JUN-11 1 27-JUN-11 1 0000000000000001

* OST : OBJECT STATUS INFORMATION.
- Contains users, resource objects and all objects
Sample Query Result
====================
268 110 Revoked 0 20-JUN-11 1 20-JUN-11 1 0000000000000000
269 110 Provisioned 1 20-JUN-11 1 20-JUN-11 1 0000000000000000
270 110 Provide Information 0 20-JUN-11 1 20-JUN-11 1 0000000000000000


**************************************************
Request Object Tables
**************************************************
* RQH - Request History Table
Sample Query Result
====================
66 41 1 Object Approval Complete 14-APR-11 1 14-APR-11 1 0000000000000000
67 41 61 181 Approved 14-APR-11 1 14-APR-11 1 0000000000000000
68 42 1 Request Initialized 14-APR-11 62 14-APR-11 62 0000000000000000
69 42 61 182 Awaiting Data 14-APR-11 62 14-APR-11 62 0000000000000000
70 42 61 182 Data Received 14-APR-11 62 14-APR-11 62 0000000000000000

* RQO - ? TODO
Sample Query Result
====================




**************************************************************************
SAMPLE QUERIES
***************************************************************************
* SELECT sdc.sdc_key, sdc.sdk_key, sdc_name, sdc_variant_type, sdc_sql_length, sdc_label, sdc_field_type, SDC_DEFAULT_VALUE, sdc_order, sdc_profile_enabled, sdc_encrypted, sdc_rowver,sdc_version, sdpv.sdp_property_value as Editable, sdpr.sdp_property_value as Optional, sdpv.sdp_property_value as Visible , sdplkv.sdp_property_value as LookupCode FROM sdk, sdc LEFT OUTER JOIN sdp_visible_v sdpv on sdc.sdc_key=sdpv.sdc_key LEFT OUTER JOIN sdp_required_v sdpr on sdc.sdc_key=sdpr.sdc_key LEFT OUTER JOIN sdp_lookupcode_v sdplkv on sdc.sdc_key=sdplkv.sdc_key WHERE sdc.sdk_key=sdk.sdk_key and (sdc.sdc_default is null or sdc.sdc_default='0') and sdc.sdc_version=0 and sdk.sdk_key=3 ORDER BY sdc_order asc, sdc.sdc_key asc;

* select ost.ost_key, ost_status from ost ost, obj obj where obj.obj_key=ost.obj_key and obj.obj_name='Request';

* select ost.ost_key, ost_status from ost ost, rqo rqo where ost.obj_key=rqo.obj_key and rqo.req_key=130;

* select * from OST where OST_STATUS='Object Approval Complete';

* select osi.orc_key, osi.mil_key, osi.sch_key, osi_rowver, sch_rowver, osi_retry_for, sch_offlined from osi osi, sch sch where osi.sch_key=sch.sch_key and sch.sch_key=1091;

* select mil_name from osi osi,sch sch,pkg pkg,tos tos,mil mil where osi.sch_key = sch.sch_key and osi.pkg_key=pkg.pkg_key and pkg.pkg_key = tos.pkg_key and tos.tos_key = mil.tos_key and pkg_type='Approval' and mil_name in('Awaiting Object Data','Awaiting Approval Data') and osi.mil_key = mil.mil_key and osi.sch_key=1091;

* select * from act act where act_name='Requests'

* select obi.obi_key, obi.obj_key, obi_status, obi_rowver, rqo_rowver, obd.obd_parent_key from rqo rqo, obi obi left outer join obd obd on obd.obd_child_key=obi.obj_key where rqo.obi_key = obi.obi_key and rqo.req_key=131 order by obd.obd_parent_key desc;

* select act_key from act act where act_name='Requests';

* select obj_autolaunch from obj where obj_key = 126;

* select pty_value from pty where pty_keyword='XL.RequestCompleteStatus';

* select orc.orc_key, orc.orc_status, oiu.oiu_key, riu.riu_key from orc orc, oiu oiu, riu riu where orc.orc_key=oiu.orc_key and riu.oiu_key=oiu.oiu_key and riu.req_key=131 and riu.obj_key=125;

* select riu.oiu_key, oiu.oiu_rowver from riu riu, oiu oiu where riu.oiu_key=oiu.oiu_key and riu.req_key=131 and riu.obj_key=125;

* select pty_value from pty where pty_keyword='XL.RequestCompleteStatus';

Table Updates
--------------
update RIU set RIU_COMPLETED=0 where riu_key=2;