【oracle】oracle锁表和解锁的sql

今天的啊彻

已于 2022-12-30 18:03:11 修改

阅读量1k

点赞数

于 2021-12-22 16:42:17 首次发布

本文链接：https://blog.csdn.net/qq_15209783/article/details/122089498

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

--oracle查看那句sql造成锁表
SELECT s.sid,
       s.serial#,
       s.event,
       a.sql_text,
       a.sql_fulltext,
       s.username,
       s.status,
       s.machine,
       s.terminal,
       s.program,
       a.executions,
       s.sql_id,
       p.spid,
       a.direct_writes
  FROM (SELECT * FROM v$session WHERE status = 'ACTIVE') s
  LEFT JOIN v$sqlarea a
    ON s.sql_id = a.sql_id
 INNER JOIN v$process p
    ON s.paddr = p.addr;
		
		
--解锁语句（sid,serial# 两个值）
alter system kill session 'sid,serial#';（其中sid=l.session_id）    
    
------oracl查看锁表
select t2.username,
       t2.sid,
       t2.serial#,
       t3.object_name,
       t2.OSUSER,
       t2.MACHINE,
       t2.PROGRAM,
       t2.LOGON_TIME,
       t2.COMMAND,
       t2.LOCKWAIT,
       t2.SADDR,
       t2.PADDR,
       t2.TADDR,
       t2.SQL_ADDRESS,
       t1.LOCKED_MODE
  from v$locked_object t1, v$session t2, dba_objects t3
 where t1.session_id = t2.sid
   and t1.object_id = t3.object_id
 order by t2.logon_time;