oracle 查看锁

SELECT *
  FROM (SELECT N.SID SID_,
               N.USERNAME 实例名,
               N.LOGON_TIME 登录打开页面时间,
               N.SQL_EXEC_START 当前语句开始执行时间,
               N.SQL_ID 当前SQLID,
               L.SQL_TEXT 当前SQL内容,
               L.SQL_FULLTEXT 当前SQL完整内容,
               N.EVENT 当前等待事件,
               N.FINAL_BLOCKING_SESSION 锁源头,
               N.MACHINE 机器名,
               N.INST_ID 节点,
               N.*,
               ROW_NUMBER() OVER(PARTITION BY N.SID ORDER BY N.LOGON_TIME, N.PREV_EXEC_START) RN
          FROM GV$SESSION N
          JOIN GV$SQL L
            ON L.SQL_ID = N.SQL_ID
         WHERE N.STATUS = 'ACTIVE'
           AND N.SCHEMANAME <> 'SYS'
         ORDER BY N.LOGON_TIME, N.PREV_EXEC_START) T
 WHERE T.RN = 1
 ORDER BY T.SQL_EXEC_START;

 

oracle查看被锁的表和解锁
--以下几个为相关表
SELECT * FROM v$lock;
SELECT * FROM v$sqlarea;
SELECT * FROM v$session;
SELECT * FROM v$process ;
SELECT * FROM v$locked_object;
SELECT * FROM all_objects;
SELECT * FROM v$session_wait;

--查看被锁的表 
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

--查看那个用户那个进程造成死锁
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;

--查看连接的进程 
SELECT sid, serial#, username, osuser FROM v$session;

--3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;

这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，
任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。

--杀掉进程 sid,serial#
alter system kill session'210,11562';
--查某session 正在执行的sql语句，从而可以快速定位到哪些操作或者代码导致事务一直进行没有结束等.

SELECT /*+ ORDERED */ 
 sql_text
  FROM v$sqltext a
 WHERE (a.hash_value, a.address) IN
       (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
               DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
          FROM v$session b
         WHERE b.sid = '67')  /* 此处67 为SID*/
 ORDER BY piece ASC;

 

 

 

 

--DBA_SOURCE字典视图：
SELECT * FROM DBA_SOURCE T WHERE T.OWNER='xx' AND T.TEXT LIKE '%XXX%'