To see if there is a lock table in Oracle Sql_oracle

1, to see if there is a lock table sql

Copy Code code as follows:

Select ' Blocker (' | | lb.sid| | ': ' | sb.username| | ') -sql: ' | | Qb.sql_text blockers,
' Waiter (' | | lw.sid| | ': ' | sw.username| | ') -sql: ' | | Qw.sql_text Waiters
From V$lock lb,
V$lock LW,
V$session SB,
v$session SW,
V$sql QB,
V$sql QW
where Lb.sid=sb.sid
and Lw.sid=sw.sid
and sb.prev_sql_addr=qb.address
and sw.sql_address=qw.address
and LB.ID1=LW.ID1
And sw.lockwait is not NULL
and sb.lockwait is null
and lb.block=1;

2, view the locked table

Copy Code code as follows:

Select P.spid,a.serial#,c.object_name,b.session_id,b.oracle_username,b.os_user_name
From V$process p,v$session A, v$locked_object b,all_objects C
where P.addr=a.paddr and a.process=b.process and c.object_id=b.object_id;

3, see that user that process caused deadlock, the level of the lock

Copy Code code as follows:

Select B.owner,b.object_name,l.session_id,l.locked_mode fromv$locked_object L, dba_objects

4, view the process of the connection

Copy Code code as follows:

SELECT SID, Serial#, username, Osuser fromv$session;

5, see which session is caused by

Copy Code code as follows:

Select B.username,b.sid,b.serial#,logon_time
From V$locked_object a,v$session b
where a.session_id = B.sid order by B.logon_time;

6. Kill the process

Copy Code code as follows:

Alter system kill session ' sid,serial# ';

SID is the SID and Serid of step 5th query