Shulou(Shulou.com)06/01 Report--

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 prompt no rows select (no row is selected), it means that there is no business lock, and the current lock does not affect the normal operation of the business system. If there are these hints, it means that there is already a lock that has affected the normal operation of the business system. According to the next operation query, it will lead to a deadlock statement and tell the business system maintenance personnel to unlock it.

B) enter the script

Select nvl (s.username _ recording Internal') username

Nvl (s.terminalreign None') terminal

L.sid | |','| | s.serial# kill

U1.name | |'. | | substr (t1.namescene 1pyr20) tab

Decode (L.lmode1) No Lock'

2 recorder Row Share'

3During Row Exclusive'

4. Write Share'

5During share Row Exclusive'

(6) lmode exclusive exclusive colors

Decode (L.requestno Lock')

2 recorder Row Share'

3During Row Exclusive'

4. Write Share'

5During share Row Exclusive'

(6) request exclusive exclusive colors

From v$lock L

V$session s

Sys.user$ u1

Sys.obj$ t1

Where L.sid = s.sid

And t1.obj# = decode (L.id2jue 0pr L.id1legL.id2)

And u1.user# = t1.owner#

And s.type! = 'BACKGROUND'

Order by 1,2,5

If the prompt no rows selected, indicates that there is no deadlock phenomenon, if there are other prompts, it means that there is a deadlock phenomenon.

You can query the SQL statement according to L.sid | |'

SELECT sql_text FROM v$sqltext a WHERE a.hash_value = (SELECT sql_hash_value FROM v$session b WHEREb.SID ='& sid')

Leave these deadlocks to the database personnel to deal with.

If you need the KILL of the resident to carry out the process, you can use the command alter system kill session 'kill field'; (the value of the kill field is the queried L.sid | |','| | s.serial# value)

3) after Kill, check the deadlock again, confirm that there is no deadlock, and close CASE.

Ps: if the KILL session is deleted, the prompt is ora-000031, and the recommended processing method is as follows:

1) use the command

Select spid, osuser, s.program

From v$session spark vested process p

Where s.paddr=p.addr and s.sid=24 (sid is L.sid | |')

ID of the queried process

2) under linux, run as root and use kill-9 12345

Under windows, orakill sid thread, where: sid: indicates the instance name to which the process to be killed belongs, and thread: is the thread number to be killed, that is, the spid queried in step 3.

Example: C: > orakill orcl 12345

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.