Unable to query some FND users in EBS 11i from front end "Users" Form

Issue:

Recently I came across into the strange issue where some FND users are unable to query from "Users" form.

Using the EBS 11i instance, and querying a user (System Administrator > Security > User > Define), the application is spinning.

Cause:

The instance was earlier configured with SSO however EBS SSO Users not changed to EBS Local User.

DBMS_LDAP: LDAP operation - waiting event due to OID was configured earlier and trying to use DBMS_LDAP while querying the user.

select USER_GUID from FND_USER where USER_NAME='KBROCK';

USER_GUID
--------------------------------
AC76C1E60F002181E040A8C00C0A063C


--Query to check session wait:

set lines 120
set pages 1000
col event for a30
select sid, event, p1, p2, p3, p1raw from v$session_wait
where event not like '%messag%' and event not in ('pipe get','PL/SQL lock timer','Streams AQ: qmn slave idle wait','Streams AQ: waiting for time management or cleanup tasks','Streams AQ: qmn coordinator idle wait')
and (wait_time=0 or state='WAITING')
order by 2
/


--Ouptut :

       SID EVENT                                  P1         P2         P3 P1RAW
---------- ------------------------------ ---------- ---------- ---------- --------
       424 DBMS_LDAP: LDAP operation               0          0          0 00
       430 DBMS_LDAP: LDAP operation               0          0          0 00
       600 pmon timer                            300          0          0 0000012C
       594 smon timer                            300          0          0 0000012C

Solution:

1. Unlink a FND_USER user account in Oracle EBusiness Suite that is linked to an SSO Account in Oracle Internet Directory

[approd@oradb ~]$ cd $FND_TOP/patch/115/sql/
[approd@oradb sql]$ ls -ltr fndssouu.sql

-rwxr-xr-x 1 approd dba 2102 Dec  1  2010 fndssouu.sql
[approd@oradb sql]$
[approd@oradb sql]$ sqlplus apps/apps

SQL*Plus: Release 8.0.6.0.0 - Production on Wed Oct 7 22:17:53 2015

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @fndssouu.sql KBROCK

PL/SQL procedure successfully completed.


Commit complete.

    2.  Kill the earlier DBMS_LDAP: LDAP operation sessions

    3.  Reset user_guid in bulk

--Script for all users having user_guid:

spool userguid.sh
SELECT 'sqlplus apps/apps  @$FND_TOP/patch/115/sql/fndssouu.sql  '||user_name ||' ;'
FROM fnd_user
WHERE user_guid IS NOT NULL
spool off;
/

$ sh userguid.sh > userguid.log

    4. Retest the issue.

Reference Metalink Notes:

• How to unlink a FND_USER user account in Oracle EBusiness Suite that is linked to an SSO   Account in Oracle Internet Directory (Doc ID 429375.1)
• Customer RecommendedHow To Change An EBS SSO User Change To EBS LOCAL User (Doc ID 1931776.1)