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

Select t2.username,t2.sid,t2.serial#,t2.logon_time

From v$locked_object T1 recording session T2

Where t1.session_id=t2.sid

Order by t2.logon_time

-- check deadlocks--

Select sess.sid,sess.serial#, lo.oracle_username,lo.os_user_name,ao.object_name,lo.locked_mode

From v$locked_object lo,dba_objects ao,v$session sess

Where ao.object_id = lo.object_id

And lo.session_id = sess.sid

-- check deadlocks--

Select decode (request, 0, 'holder:',' waiter:') | | sid sid,id1,id2,lmode,request,type,ctime/60 lock time

From v$lock where (id1, id2, type) in

(select id1, id2, type from v$lock where request > 0) order by id1, request

-- obtain process details according to sid--

SELECT a.username,a.machine,a.program,b.spid,a.sid,a.serial#,a.status,c.piece,c.sql_text

FROM v$session a, v$process b, v$sqltext c

WHERE a.sid = '749'

AND b.addr = a.paddr

AND a.sql_address = c.address (+)

ORDER BY c.piece

-- add 2012-09-14

-- the solution to data table deadlock (turn)

Deadlock is a common problem in database, database generally does not have deadlock for no reason, deadlock is usually caused by the design of our application. When a deadlock occurs, here are the general solutions: 1) execute the following SQL to see which tables are locked first: select b.ownerstatement b.objectroomname.sessiondeadidrecovera.lockedcallimodefrom v$locked_object adbaiting objects bwhere b.object_id = a.object_id 2) check and deal with the session that caused the deadlock, select b.usernamereachedb.sidrecoveryb.serializeditylogonedtimetimefrom v$locked_object atime.villustrating session bwhere a.session_id = b.sid order by b.logonroomtime.This will list SID3) find SID and SERIAL#: check the V$SESSION view: SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID=' will get PADDR4 for the SID'; that SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID=' has just found.) check the V$PROCESS view: PADDR' that SELECT SPID FROM V$PROCESS WHERE ADDR=' has just found This step is SPID5) kill the process (1) in the database, kill the ORACLE process: the SID found by ALTER SYSTEM KILL SESSION', the SERIAL#'; found out (2) if we can't kill the process in ORACLE, we can only go to the operating system and use operating system commands to kill process KILL-9 "SPID just found" on the WINDOWS platform, it can be even that orakill.

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.