Well I've told you how to kill a session from the database, whatever the reason. One of the reasons may be because it's a blocking session.
There are many ways of finding blocking sessions. I personally use Toad for Oracle for browsing the database, it has a Session Browser, and a Locks tab to find locking sessions.
Also the following sql will give you the waiting and holding sessions on your instance:
SELECT w.*,
(SELECT status
FROM v$session ses
WHERE ses.sid = w.holding_session)
holding_ses_status,
(SELECT sql_text
FROM v$session ses, v$sql sq
WHERE ses.sql_address = sq.address
and ses.sql_hash_value=sq.hash_value
AND ses.sid = w.waiting_session
AND ROWNUM = 1)
waiting_sql,
(SELECT sql_text
FROM v$session ses, v$sql sq
WHERE ses.sql_address = sq.address
and ses.sql_hash_value=sq.hash_value
AND ses.sid = w.holding_session
AND ROWNUM = 1)
holding_sql
FROM dba_waiters w
This sql will give you both the ORACLE SID of both the sessions and the current sql both of them are currently running.
If you want to detect developer sessions on the production database, the following sql can be used:
select ses.sid,pr.spid, ses.status, ses.schemaname, ses.machine, ses.terminal, ses.osuser,ses.program,ses.module, ses.action , ses.logon_time
from v$session ses,
v$process pr
where ses.paddr=pr.addr
and lower(ses.program) in ('plsqldev.exe','toad.exe','sql developer')
order by pr.pga_used_mem desc
It depends on the company profile if you are allowed or not to kill sessions, it's better to know that before you try to kill any session, or sometimes it's better to give them a report with what session is holding another session.
No comments:
Post a Comment