As a developer I have had to go through some trying times when a program goes on forever and I need to terminate it but I could not from the front end. Other times, the package itself was locked by a ghost session and am unable to recompile the package.
First I had to see how many sessions are running on a particular package/procedure/function using the following SQL:
1
2
3
4
5
6
7
8
| SELECT * FROM v$sqltext WHERE hash_value = (SELECT sql_hash_value FROM v$session WHERE SID = (SELECT SID FROM v$access WHERE OBJECT = 'XXEYPO_WF_ATTACHMENTS_PKG' AND owner = 'APPS'))ORDER BY piece |
SQLs to identify and kill the sessions. Note: Once a session is killed it will terminate the Apps front end session as well.
Get the sesssion id which are accessing the code:
1
| select * from v$access where object = 'XXEYPO_WF_ATTACHMENTS_PKG' |
Then get the session details for the SID
1
| SELECT sid, serial#, SQL_HASH_VALUE FROM V$SESSION WHERE SID = '103' |
Now you have both the SID and SERIAL# for the session. You can identify the session uniquely and kill it.
1
| alter system kill session '103, 47832' -- 'SID, serial#' |
0 comments