ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired ORA-06512: at "APPS.WF_NOTIFICATION", line 5130 ORA-06512: at line 1

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired ORA-06512: at "APPS.WF_NOTIFICATION", line 5130 ORA-06512: at line 1

Approve Workflow Notification Mailer --

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired ORA-06512: at "APPS.WF_NOTIFICATION", line 5130 ORA-06512: at line 1

Solution :

1.
select do.owner,do.object_name,do.object_type,dl.session_id,vs.serial#, vs.program,vs.machine,vs.osuser
from dba_locks dl,dba_objects do,v$session vs
where do.object_name ='WF_NOTIFICATIONS' and do.object_type='TABLE' and dl.lock_id1 =do.object_id and vs.sid = dl.session_id;

Issue the command

sql > alter system kill sessions 'sid,serial#' immediate;

2. 
If you dont see anything in the query then there is some DML operation being performed in your system for the same process.

i.e.
1. Process A is running
2. Process A locked an Object.
3. Process B gets triggered.
4. Process B is blocked due to process A and throws the error.

Check Workflow components(Notification mailers, listeners etc) which are not running in Oracle Apps

Use the below query to Check Workflow components(Notification mailers, listeners etc) which are not running From back-end:-


  SELECT component_type,
         component_name,
         Component_status,
         COMPONENT_STATUS_INFO Error
    FROM fnd_svc_components
   WHERE component_type LIKE 'WF%' AND component_status <> 'RUNNING'
ORDER BY 1 DESC, 2, 3;

ORA-20002: Version of statistics table "APPLSYS"."FND_STATTAB" is too old. Please try upgrading it with dbms_stats.upgrade_stat_table ORA-06512: at "APPS.FND_STATS", line 2828 ORA-06512: at line 1

This issue could come when you upgrade your DB to 12c or higher version.

To Replicate the issue gather stats on a table, Upgrade DB to 12c, create new index on the table and try to gather stats using below piece of code.

Gather Stats :-

exec fnd_stats.GATHER_TABLE_STATS(ownname=>'XXNAME',tabname=>'XX_NAME_TBL', PERCENT=>20,degree=>32,backup_FLAG=>'BACKUP',cascade=>true,invalidate=>'Y');

OR

BEGIN fnd_stats.GATHER_TABLE_STATS(ownname=>'XXNAME',tabname=>'XX_NAME_TBL', PERCENT=>20,degree=>32,backup_FLAG=>'BACKUP',cascade=>true,invalidate=>'Y'); END;

Error :-

ORA-20002: Version of statistics table "APPLSYS"."FND_STATTAB" is too old.

Please try upgrading it with dbms_stats.upgrade_stat_table

ORA-06512: at "APPS.FND_STATS", line 2828

ORA-06512: at line 1

Solution :-

1. Connect to sqlplus / as sysdba

2. Backup FND_STATTAB table

3. run: EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE(‘APPLSYS’,’FND_STATTAB’);

Now try to gather stats on your table.