|
1 2 |
Select sid,serial#,taddr,username,osuser,lockwait,status,machine,SQL_ADDRESS,SQL_ID,PROCESS from v$session where lockwait is not null order by SID; |
|
1 |
Alter system kill session ‘sid nimber,serial# number’;a |
|
1 2 |
select ‘Alter system kill session ”’||sid||’,’||serial#||”’;’ from v$session where lockwait is not null order by SID; |
|
1 2 3 4 5 6 7 8 |
select s.sid, s.serial#, p.spid from v$session s, v$process p where s.paddr = p.addr and s.sid in (select SESSION_ID from v$locked_object); |
Getting the SID and Serial number of the locking and locked objects
|
1 2 3 |
select sid, serial# from v$session where sid IN (select holding_session from dba_waiters); |
Finding and killing sessions, according to the os users
|
1 |
Select ‘alter system kill session ”’||sid||’,’||serial#||”’ immediate ;’ from v$session where lockwait is not null order by osuser; |
A view to see the locks
|
1 2 |
select * from dba_lockers select * from dba_waiters (waiting_session, holding_session) |
|
1 2 3 4 |
select l1.sid, ‘ IS BLOCKING ‘, l2.sidfrom v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2 |
|
1 2 3 4 5 6 7 8 |
select s1.username || ‘@’ || s1.machine || ‘ ( SID=’ || s1.sid || ‘ ) is blocking ‘ || s2.username || ‘@’ || s2.machine || ‘ ( SID=’ || s2.sid || ‘ ) ‘ AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ; |
|
1 2 |
select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# 2* from v$session where sid=479 ; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SET LINESIZE 100 COLUMN spid FORMAT A10 COLUMN username FORMAT A10 COLUMN program FORMAT A45 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’; |
|
1 |
ALTER SYSTEM KILL SESSION ‘115,15268’ immediate; |
|
1 |
ALTER SYSTEM KILL SESSION ‘sid,serial#,@inst_id’; |
|
1 |
ALTER SYSTEM KILL SESSION ‘sid,serial#’ IMMEDIATE; |
|
1 |
ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ POST_TRANSACTION; |
|
1 |
ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ IMMEDIATE; |
|
1 |
alter system disconnect session ‘30,7’; |
|
1 2 3 |
orakill ORACLE_SID spid kill -9 spid ps -ef | grep ora |
|
1 |
vmstat 2 |
|
1 2 3 4 5 6 |
sar -u 2 5 sar -P ALL sar -d 2 sar -d | grep dev3 sar -r -n DEV -f /var/log/sa/sa16 — writes to to file sa16 sar -n DEV | more |
The following SQL query can be used to find the numbers of any hanging processes in the V_$LOCKED_OBJECT view.
|
1 2 |
Select sid,serial#,taddr,username,osuser,lockwait,status,machine,SQL_ADDRESS,SQL_ID,PROCESS from v$session where process=’896:904′ order by osuser; |
Check the query from the SQL_ADDRESS column.
|
1 |
Alter system kill session ‘sid number,serial#’ immediate; |
The previous line should end the session. If there’s no response, end the process according to operating system user.
The following query can be used to find the process ID through the operating system.
|
1 2 3 4 5 6 7 |
select sid, spid from v$process p, v$session s where paddr = addr and s.sid in(XXXX) order by s.sid; |
After connecting as a root user:
|
1 |
#kill -9 spid |
Using RMAN for...
12 March 2019