Running ADFIXUSER.sql Deadlocks With ORA-00060 (Doc ID 2028437.1)

Last updated on NOVEMBER 02, 2016

Applies to:

Oracle Applications Technology Stack - Version 12.1.3 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.

Symptoms

On Oracle Applications 12.1.3 version, Database for Application Technology,
running ADFIXUSER.sql deadlocks with:
ORA-00060: deadlock detected while waiting for resource, ORA-06512: at "SYS.AD_ZD_SYS", line 39

This can be reproduced with the following steps:
1. Run the EBS (12.1.3) script ADFIXUSER.sql which calls sys.ad_zd_sys.fix_sysuser;

sqlplus -s APPS/**** @/u01/applmgr/patch/apps/apps_st/appl/ad/12.0.0/patch/115/sql/ADFIXUSER.sql

2. The following error results:

begin
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "SYS.AD_ZD_SYS", line 39
ORA-06512: at line 2

3. The locks appear to be on these objects:

USERNAME TERMINAL KILL TABLE_LOCKED LOCK_MODE
--------- ------------ --------- ------------------------- - ------------------
SYS UNKNOWN 1149,7 SYS.WRI$_ADV_OBJSPACE_TR Row Exclusive
SYS UNKNOWN 1149,7 SYS.WRI$_ADV_OBJECTS Row Exclusive
SYS UNKNOWN 1149,7 SYS._NEXT_OBJECT Row Exclusive
SYS UNKNOWN 1149,7 SYS._NEXT_OBJECT Row Exclusive
SYS UNKNOWN 1149,7 SYS.ORA$BASE Share
SYS UNKNOWN 1149,7 HR.IRC_POSTING_CONTENTS Share

 

The locked objects can be identified by running:

select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from (select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,(select object_id, owner, object_name,object_type from dba_objects) b where a.object_id=b.object_id;

 

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