where q.address = s.sql_address
and Q.hash_value = S.sql_hash_value
and s.paddr = P.addr
and exists (select SID
From V$lock
where Block=1 and Sid=s.sid);
If you are prompted no rows select (No row selected), then there is no business aspect of the lock, the current lock does not affect the normal operation of the business system, if you have these prompts, indicating that the existing lock has affected the normal operation of the business system, according to the next operation query leads to deadlock statements, Business system maintenance personnel to unlock;
b) Input Script
Select NVL (s.username, ' Internal ') Username,
NVL (s.terminal, ' None ') terminal,
l.sid| | ', ' | | S.serial# Kill,
u1.name| | '. ' | | SUBSTR (t1.name,1,20) tab,
Decode (l.lmode,1, ' No Lock ',
2, ' Row Share ',
3, ' Row Exclusive ',
4, ' Share ',
5, ' Share Row Exclusive ',
6, ' Exclusive ', null) Lmode,
Decode (l.request,1, ' No Lock ',
2, ' Row Share ',
3, ' Row Exclusive ',
4, ' Share ',
5, ' Share Row Exclusive ',
6, ' Exclusive ', null) request
From V$lock L,
V$session S,
sys.user$ U1,
sys.obj$ T1
where L.sid = S.sid
and t1.obj# = decode (L.ID2,0,L.ID1,L.ID2)
and u1.user# = t1.owner#
and s.type! = ' BACKGROUND '
Order BY 1,2,5;
If no rows selected is indicated, then there is no deadlock, and if there are other hints, there is a deadlock phenomenon.
Available according to l.sid| | ' To query the SQL statement:
Select Sql_text from V$sqltext a WHERE A.hash_value = (SELECT sql_hash_value from v$session b whereb.sid = ' &sid ')
Give these deadlocks to the database personnel for processing,
If a resident person is required to kill the process, you can use the command alter system kill session ' kill field value '; (The value of the Kill field is the queried l.sid| | ', ' | | s.serial# value)
3) after kill, look again for the deadlock condition, confirm that there is no deadlock, close case.
Ps: If you delete the kill session and prompt for ora-000031, the recommended processing method is as follows:
1) Use the command
Select spid, Osuser, S.program
From V$session s,v$process p
where S.paddr=p.addr and s.sid=24 (SID is L.sid| | ' ）
Process ID of the query
2) under Linux, run as root and use kill-9 12345
Under Windows, the Orakill SID thread, where: Sid: Represents the instance name of the process to be killed, thread: is the number of threads to be killed, that is, the SPID that the 3rd step queries out.
Example: C:>orakill ORCL 12345
Discover how the operating system's database deadlock is handled
