Issues Caused Due To AUDIT ON WF_USER_ROLE_ASSIGNMENTS & WF_LOCAL_USER_ROLES (Doc ID 1326404.1)

Last updated on NOVEMBER 09, 2016

Applies to:

Oracle Workflow - Version 11.5.10.2 to 12.2 [Release 11.5.10 to 12.2]
Information in this document applies to any platform.
Checked for relevance on 06-MAR-2013

Symptoms

Scenario 1: AUDIT ON WF_USER_ROLE_ASSIGNMENTS

Query on WF_USER_ROLE_ASSIGNMENTS errors out

SELECT /*+ LEADING(o) PUSH_SUBQ */
o.assigning_role, o.partition_id, o.relationship_id, o.user_name,
o.audit_timestamp, o.audit_sequence_id, o.audit_session_id,
o.audit_transaction_type, o.audit_user_name, o.audit_commit_id,
o.row_key, n2.end_date, n3.start_date
FROM wf_user_role_assignments_av1 o,
wf_user_role_assignments_av2 n2,
wf_user_role_assignments_av3 n3
WHERE 1 = 1
AND n2.assigning_role = o.assigning_role
AND n2.partition_id = o.partition_id
AND n2.relationship_id = o.relationship_id
AND n2.user_name = o.user_name
AND n2.row_key =
(SELECT row_key
FROM wf_user_role_assignments_av2 e
WHERE e.assigning_role = o.assigning_role
AND e.partition_id = o.partition_id
AND e.relationship_id = o.relationship_id
AND e.user_name = o.user_name
AND e.row_key > o.row_key
AND ROWNUM = 1)
AND n3.assigning_role = o.assigning_role
AND n3.partition_id = o.partition_id
AND n3.relationship_id = o.relationship_id
AND n3.user_name = o.user_name
AND n3.row_key =
(SELECT row_key
FROM wf_user_role_assignments_av3 e
WHERE e.assigning_role = o.assigning_role
AND e.partition_id = o.partition_id
AND e.relationship_id = o.relationship_id
AND e.user_name = o.user_name
AND e.row_key > o.row_key
AND ROWNUM = 1)



Error : ORA-08103: object no longer exists&ORA-01410: invalid ROWID error while executing a select query


Scenario 2 : AUDIT ON WF_LOCAL_USER_ROLES

TIME STAMP MISMATCH IN WF_LOCAL_USER_ROLES and WF_LOCAL_USER_ROLES_A

Example : User has 3 responsibilities, 2 of which have been last updated 21-APR-2011 according to WF_LOCAL_USER_ROLES table. However in WF_LOCAL_USER_ROLES_A audit table,
audit_timestamp for all 3 responsibilities state that the last update was on 25-APR-2011.

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms