My Oracle Support Banner

12.2 E-Business Suite Setting Profile Sign-On:Audit Level Causes Deadlocks | Locks On FND_LOGINS (Doc ID 1938090.1)

Last updated on JULY 20, 2024

Applies to:

Oracle E-Business Suite Technology Stack - Version 12.2 and later
Oracle Application Object Library - Version 12.2 and later
Information in this document applies to any platform.

Symptoms

E-Business Suite 12.2 Applications Object Library, Basic Sysadmin Functions and Maintenance issues

 

 


When attempting to connect with many users at the same time to the system there are several deadlocks as follows:

DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
 
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
 
Deadlock graph:
  ---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-000e0015-000ac180 759 4108 X 787 2000 X
TX-004a001f-000ab6cf 787 2000 X 759 4108 X
 
session 4108: DID 0001-02F7-0000006A session 2000: DID 0001-0313-0000006A
session 2000: DID 0001-0313-0000006A session 4108: DID 0001-02F7-0000006A
 
Rows waited on:
  Session <ID>: obj - rowid = <ID> - AAB5LeAGOAAAAE8AAC
  (dictionary objn - 34627, file - 398, block - 316, slot - 2)
  Session <ID>: obj - rowid = <ID> - AAB5LeAGOAAACetAAg
  (dictionary objn - 34627, file - 398, block - 10157, slot - 32)
 
----- Information for the OTHER waiting sessions -----
Session 2000:
  sid: <ID> ser: <ID> audsid: <ID> user: <ID>
  flags: (0x8000041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
  flags2: (0x40009) -/-/INC
  pid: 787 O/S info: user: oracle, term: UNKNOWN, ospid: <ID>
  image: oracle@<hostname>
  client details:
  O/S info: user: applmgr, term: unknown, ospid: 1234
  machine: <hostname> program: JDBC Thin Client
  client info: 85 0
  application name: e::bes:oracle.apps.icx.security.session.created, hash value=<ID>
  action name: /, hash value=<ID>
  current SQL:
  UPDATE FND_LOGINS FL SET END_TIME = :B2 WHERE SPID = :B1
 
----- End of information for the OTHER waiting sessions -----
 
Information for THIS session:
 
----- Current SQL Statement for this session (sql_id=<ID>) -----
UPDATE FND_LOGINS FL SET END_TIME = :B2 WHERE SPID = :B1
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object line object
  handle number name
0x35db217d0 107 package body APPS.FND_SIGNON
0x35db217d0 334 package body APPS.FND_SIGNON
0x355e42420 2086 package body APPS.FND_SESSION_MANAGEMENT
0x3598e4960 1 anonymous block

 
The issue can be reproduced at will with the following steps:
1. Set the profile option : Sign-On:Audit Level" to FORMS (any option different then NONE).
2. Connect with many users at the same time.
3. Error thrown in the database log file.

The issue has the following business impact:
Due to this issue, users cannot work when using the AUdit functionality as there are far too many deadlocks.

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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

In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.