Check whether there is an sql for the lock table in Oracle
- 2021-09-25 00:01:58
- OfStack
1. Check whether there is an sql for the lock table
2. View locked tables
3. Check which user and which process caused deadlock and the level of lock
4. View the connected process
5. See which session caused it
6. Kill the process
sid is the sid and serid found in Step 5
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 locked tables
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. Check which user and which process caused deadlock and the level of lock
select b.owner,b.object_name,l.session_id,l.locked_mode fromv$locked_object l, dba_objects
4. View the connected process
SELECT sid, serial#, username, osuser FROMv$session;
5. See which session caused it
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
alter system kill session 'sid,serial#';
sid is the sid and serid found in Step 5