Looking for an immediate help as this issue is in Production.
I suspect that someone migrated your custom package while there was a pl/sql lock on it, which will cause these errors. Please run the following to see if there are access locks:
SELECT fu.user_name "APPS Username", fu.email_address "Email", vs.username "DB Username", vs.machine "Client Host", vs.process "Client Process",
vs.sid "DB SID", vs.serial# "DB Serial #", vs.program "Program", vs.MODULE "Module", vs.action "Action", to_char(vs.logon_time,'MM/DD/YYYY HH24:MI') "Login Time",
vp.spid "Server Process"
FROM v$session vs, v$process vp, fnd_logins fl, fnd_user fu, v$access va
WHERE vp.addr(+) = vs.paddr
AND fl.spid(+) = vs.process
AND vs.sid = va.sid
AND fl.end_time(+) IS NULL
AND fu.user_id(+) = fl.user_id
AND va.object = upper('XX_OM_WORKFLOW');
Kill any sessions that are accessing that package. When this reports no rows returned, recompile the package with
alter package XX_OM_WORKFLOW compile;
alter package XX_OM_WORKFLOW compile body;
Thank you for responding. There were no locks when the package was migrated. Also there will be easily 7000 order line workflows that are processed in a day. We are getting this error for 50 - 100 lines and mostly for one user. Nothing is different for this user. How can we track that user activities?
1.This will help us to identify the problematic package which cause ora- error
SQL> alter system set events '4061 trace name errorstack';
1.1 you need to review all the trace files from user_dump_dest look for ora- error.
2. Please ensure you enable errorstack for short period and reproduce the problem quickly.
Please use this to disable it.
SQL> alter system set events '4061 trace name errorstack off';
Packages are unfortunately loaded into memory by the workflow processes, then can be invalidated by changes to the database. The database object will recompile (either automatically when next accessed, or manually by the DBA), however the version in memory doesn't. We have had similar issues in the past, so now when we deploy anything that invalidates an object used in workflow we do this:
- Stop the concurrent managers
- Flush the shared pool
- Deploy the new object
- Start the concurrent managers
and that seems to resolve the problem. So I would suggest doing that for now - stopping the CM's then flushing the shared pool. Failing that, you'll have to bounce the instance (both tiers). There's a bunch of MOS notes about this issue from memory (I trawled through a load of them when we hit the problem last time!).