Who's Online Now
0 registered members (), 3 guests, and 19 spiders.
Key: Admin, Global Mod, Mod
Newest Members
SAI, Balasubramaniam, Dataterrain, Sarathy R, pjchiverton
1258 Registered Users
Recent Posts
Warning: VAT scam
by CT. 19/10/17 09:27 AM
EDI Replacement
by PaulD. 12/10/17 10:27 AM
Future dated records and API
by hpatel15. 10/10/17 08:04 AM
Faster Payments
by pat. 04/10/17 08:56 AM
Additional Org Info: Org Developer DF
by Sarathy R. 04/10/17 06:54 AM
Monthly Data Collection
by hpatel15. 28/09/17 07:33 AM
Top Posters(30 Days)
SBi 2
PaulD 2
CT 1
delboy 1
pat 1
Bobin 1
Popular Topics(Views)
417,709 PAYE RTI
49,884 HR_PF.K RUP4
Previous Thread
Next Thread
Print Thread
Rate This Thread
#9187 - 20/03/13 09:54 AM V$SESSION and FND_SESSIONS Fun  
Joined: Jan 2013
Posts: 79
Bagpuss Offline
hacker
Bagpuss  Offline
hacker

Joined: Jan 2013
Posts: 79
Bath, England
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

#9198 - 25/03/13 08:31 AM Re: V$SESSION and FND_SESSIONS Fun [Re: Bagpuss]  
Joined: Jan 2013
Posts: 79
Bagpuss Offline
hacker
Bagpuss  Offline
hacker

Joined: Jan 2013
Posts: 79
Bath, England
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.


#9199 - 25/03/13 09:08 AM Re: V$SESSION and FND_SESSIONS Fun [Re: Bagpuss]  
Joined: Mar 2005
Posts: 2,086
CT Offline
CT  Offline

****

Joined: Mar 2005
Posts: 2,086
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...


CT
#9208 - 25/03/13 09:26 PM Re: V$SESSION and FND_SESSIONS Fun [Re: CT]  
Joined: Jan 2013
Posts: 79
Bagpuss Offline
hacker
Bagpuss  Offline
hacker

Joined: Jan 2013
Posts: 79
Bath, England
Originally Posted by CT
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!

#9218 - 26/03/13 11:48 AM Re: V$SESSION and FND_SESSIONS Fun [Re: Bagpuss]  
Joined: Jan 2013
Posts: 79
Bagpuss Offline
hacker
Bagpuss  Offline
hacker

Joined: Jan 2013
Posts: 79
Bath, England
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.


Moderated by  bcooper, CT 

Forum Statistics
Forums48
Topics2,193
Posts11,868
Members1,258
Most Online90
Mar 5th, 2017
Today's Birthdays
No Birthdays
Recent vacancies
Top Posters(All Time)
CT 2,086
bcooper 1,285
delboy 1,250
paulgos 431
SBi 425
Gus 252
pat 249
vkumar 223
October
S M T W T F S
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31
Powered by UBB.threads™ PHP Forum Software 7.6.0
Page Time: 0.022s Queries: 15 (0.006s) Memory: 2.7523 MB (Peak: 2.9123 MB) Zlib disabled. Server Time: 2017-10-21 19:37:53 UTC