| 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; |
