Identify the offending session using the V$SESSION or GV$SESSION

posted Sep 8, 2010, 8:07 AM by Sachchida Ojha
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';

ALTER SYSTEM KILL SESSION

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

 In a RAC environment

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#@inst_id';

 The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible.

 SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

 This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.  

 Show which row is locked

 select do.object_name
, row_wait_obj#
, row_wait_file#
, row_wait_block#
, row_wait_row#
, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
    ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
from v$session s
, dba_objects do
where sid=&sid
and  s.ROW_WAIT_OBJ# = do.OBJECT_ID
/

List locks


column lock_type format a12
column mode_held format a10
column mode_requested format a10
column blocking_others format a20
column username format a10
SELECT session_id
, lock_type
, mode_held
, mode_requested
, blocking_others
, lock_id1
FROM dba_lock l
WHERE  lock_type NOT IN ('Media Recovery', 'Redo Thread')
/

The DBA would be able to query the SID and Serial# for the user session from the v$session dynamic view. There are many reasons why the DBA would want to use OS Kill from the command-line rather than using alter system kill session... command:

 1.Clearing Locks - Using alter system kill session... will not clear any locks that may exist. Oracle will keep the session connected until the user session times out. It is only then that Oracle releases any locks. With OS Kill, the thread will be killed and all locks held by that thread released.

 2.Unable to Connect to Oracle - It may be that the DBA cannot gain access to the database to obtain the SID and Serial# for the user session. It may be because a runaway process is consuming all available resources (CPU, memory, I/O). With OS Kill, if the DBA knows the SPID, they can simply kill the user session from the command-line. But how does the DBA find the SPID if he cannot log into the database.There are many third party utility available to find out SPID. You can also use OEM Grid control.

ALTER SYSTEM DISCONNECT SESSION
 
Oracle 11g introduced the ALTER SYSTEM DISCONNECT SESSION syntax as a new method for killing Oracle sessions. Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSION  command kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system. The basic syntax is similar to the KILL SESSION command with the addition of the POST_TRANSACTION clause. The SID and SERIAL# values of the relevant session can be substituted into one of the following statements.
 
If after a few minutes the process hasn't stopped, terminate the session using the following.
 
kill -9 spid
Show locked objects
select oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id
, owner || '.' || object_name object
, object_type
, decode( l.block
 , 0, 'Not Blocking'
 , 1, 'Blocking'
 , 2, 'Global') status
, decode(v.locked_mode
 , 0, 'None'
 , 1, 'Null'
 , 2, 'Row-S (SS)'
 , 3, 'Row-X (SX)'
 , 4, 'Share'
 , 5, 'S/Row-X (SSX)'
 , 6, 'Exclusive', TO_CHAR(lmode)) mode_held
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where  v.object_id = d.object_id
and  v.object_id = l.id1
and  v.session_id = s.sid
order by oracle_username
, session_id
/
SELECT a.username, a.osuser, b.spid
FROM   v$session a, v$process b
WHERE  a.paddr = b.addr
 AND   a.username IS NOT null;
Comments