Hi,

I need a session on sessions! We had an issue whereby a session in V$SESSION of about two weeks vintage was related to duplicate entries (in terms of session_id = audsid) in the FND_SESSIONS table. This led to a unique constraint violation when users were trying to access Letters in payroll.

The DBAs were reluctant to kill the session without some assurances from Oracle but as cowboy techies we thought that would solve the issue. Luckily, the session commited suicide overnight anyway and today all is well again.

We haven't yet established which process is creating duplicate sessions in FND_SESSIONS - there were 77 for the now defunct V$SESSION!

There are other duplicates in there (usually just a pair) and they all seem to be related to JDBC connections.

It seemed to start after we applied RUP7 in February but that may be a coincidence. We are running EBS 11i.

Should every entry in FND_SESSIONS have a corresponding row in V$SESSION? Any suggestions on why duplicate session_)id values exist in that table and how to determine what created them?

Any tips gratefully received!

Many thanks,

Bagpuss

After weeks of fun, we traced it to a bug in the PAY_BATCH_ELEMENT_ENTRY_API (v115.26). Now convincing Oracle of that fact. The API is inserting a row into FND_SESSIONS if a row doesn't exist with the same effective date - irrespective of whether a row with the same session ID exists (it should update this row in such cases). This leads to the duplicate session IDs in FND_SESSIONS, causing our issue (ORA-1427) when using FF_GLOBALS in Fast Formula that are attached to elements we are processing via the BEE APIs.

This was OK prior to RUP7 (v115.22 of the API doesn't do anything with FND_SESSIONS) - hope this helps others who may stumble across this issue.

Good spot, Mr Bagpuss.

Amazing to think that they only had to look at the hr_employee_api.create_gb_employee to see an example of how to go about doing exactly that.

FYI this API maintains (i.e. either creates or updates as appropriate) an entry in FND_SESSIONS to allow the validation of NI numbers to work properly (this is done via a Fast*Formula, which needs an effective date setting up in order to work correctly). Oddly enough, it removes the row after the API is complete...

What makes it worse is that Oracle have a nice procedure, hr_integration_utils.add_or_update_session I think, to do it neatly and properly!

After the usual sparring with Oracle Support, they've now finally conceded this is a bug (11i = 16545299) so hopefully it'll be sorted soon. I think there's a concurrent program to cleanse FND_SESSIONS, so we'll have a look at that once the patch is in.