EBS_DBA_查询:锁相关

最新推荐文章于 2025-08-15 09:00:49 发布

weixin_33887443

最新推荐文章于 2025-08-15 09:00:49 发布

阅读量129

点赞数

CC 4.0 BY-SA版权

文章标签：数据库操作系统

原文链接：http://www.cnblogs.com/hopedba/p/5794574.html

本文提供了一系列Oracle SQL查询语句，用于诊断和解决Oracle数据库中的锁问题。包括查询锁定对象、锁定会话信息、锁定类型及模式等，帮助DBA快速定位并解决由锁引起的性能瓶颈。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select DISTINCT lk.session_id sid,
 se.serial#,
 lk.locked_mode lk_md,
 '|',
  se.CLIENT_IDENTIFIER c_name, --BES系统登录名
  dd.FULL_NAME,
  se.logon_time,
  se.ACTION,
  FV.RESPONSIBILITY_NAME transfer,
  lk.oracle_username || '_' || lk.os_user_name || '_' ||se.machine user_info,
  '|',
  se.MODULE,
  BF.FORM_NAME,
  --substr(se.MODULE, instr(se.MODULE, ':', 1, 3) + 1, length(se.MODULE)),
  FT.USER_FORM_NAME,
  FT.LANGUAGE,
  se.WAIT_CLASS,
  sw.event,
  la.name,
  sa.sql_text,
  '|',
  'alter system kill session ''' || se.sid || ',' || se.serial# || ''';' killsql,
  c.spid AS os_process_id,
  c.pid,
  'ps -ef | grep ' || c.spid as check_os_process_command,
  'kill -9 ' || c.spid as kill_os_process_command
FROM v$locked_object  lk,
 v$session   SE,
 v$sqlarea   sa,
 v$session_wait  sw,
 v$latch    la,
 v$process   c,
 per_people_f   dd,
 FND_USER   FU,
 FND_RESPONSIBILITY_VL fv,
 FND_FORM_TL    FT,
 FND_FORM   BF
WHERE 1 = 1
 AND se.sid = lk.session_id
 AND se.prev_sql_addr = sa.address
 AND se.sid = sw.sid
 AND se.paddr = c.addr
 AND sw.p2 = la.latch#(+)
 AND se.CLIENT_IDENTIFIER = FU.user_name
 AND fu.EMPLOYEE_ID = DD.PERSON_ID
 AND substr(se.ACTION, instr(se.ACTION, '/', 1, 1) + 1, length(se.action))=FV.RESPONSIBILITY_KEY(+)
 AND substr(se.MODULE, instr(se.MODULE, ':', 1, 3) + 1, length(se.MODULE))= BF.FORM_NAME(+)
 AND BF.FORM_ID = FT.FORM_ID(+)
 AND FT.LANGUAGE(+) = USERENV('LANG')
ORDER BY se.LOGON_TIME,sid

应当注意对于sid在100以下的应当谨慎，可能该进程对应某个application，如对应某个事务,可以kill. 可以用Spotlight软件对数据库的运行状态进行监控。
oracle会话被锁是经常的。可以使用alter system kill session 'sid,serial#'杀死会话,   如果有长期出现的一列，可能是没有释放的锁。我们可以用下面SQL语句杀掉长期没有释放非正常的锁：如果出现了锁的问题, 某个DML操作可能等待很久没有反应。
当你采用的是直接连接数据库的方式，也不要用OS系统命令 $kill process_num 或者 $kill -9 process_num来终止用户连接，因为一个用户进程可能产生一个以上的锁, 杀OS进程并不能彻底清除锁的问题。
--解除锁定 --1.强制:
alter system kill session 'sid,serial#';
--2.当占用锁会话commit时,解除锁定alter system kill session 'sid,serial#';
==============================================
--以下几个为相关表  
 
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;  
===================================================
--1.查出锁定object的session的信息以及被锁定的object名,生    
select b.owner,
      b.object_name,
        x.WAIT_CLASS,--引起等待的类
      l.session_id,
      x.SERIAL#,
        l.locked_mode 锁定模式,
        lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username DB用户,
      l.os_user_name OS用户,
      x.username,
      x.logon_time,
      x.machine 机器名,
      x.terminal,
      x.client_info,
        x.logon_time,
      'alter system kill session ''' || x.sid || ',' || x.serial# || ''';' killsql,
      c.spid AS os_process_id,
      c.pid,
      'ps -ef | grep ' || c.spid as check_os_process_command,
      'kill -9 ' || c.spid as kill_os_process_command
from v$locked_object l, dba_objects b, v$session x, v$process c
where b.object_id = l.object_id
 and l.SESSION_ID = x.SID
 and c.addr = x.paddr
--and OBJECT_NAME like 'MTL_ITEM_LOCATIONS'
order by b.object_id,l.xidusn DESC;

--如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待(左边锁右边等待?????)  
--以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。  
--如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN
--还可以根据v$session_wait;查看等待事件
--2.查出锁定表的session的sid, serial#,os_user_name, machine name, terminal和执行的语句  
 
 
SELECT lk.session_id sid,
      se.serial#,
       lpad(' ', decode(lk.xidusn, 0, 3, 0)) || lk.oracle_username User_name,
        lk.locked_mode,
      lk.oracle_username,
      se.user#,
      lk.os_user_name,
      se.machine,
      se.terminal,
      a.sql_text,
      a.action
FROM v$sqlarea a, v$session se, v$locked_object lk
WHERE lk.session_id = se.sid
 AND se.prev_sql_addr = a.address
--  and sid= &sid      --利用sid找到相应的sql
ORDER BY sid, se.serial#;
 
--3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode  
--这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，  
--任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。
SELECT s.sid, --会话ID
      s.serial#,
      s.username,
      decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', l.type) LOCK_LEVEL,
      o.object_name, --对象名称
      o.object_type, --对象类型
      l.lmode,
      l.request,
      o.owner, --对象用户
      s.schemaname,
      s.osuser, --操作系统用户
      s.process,
      s.machine, --对象机器名域名
      s.terminal, --对象名称
      s.program, --对象通过程序
      s.logon_time,
      l.block
 FROM v$session s, v$lock l, dba_objects o
WHERE l.sid = s.sid
  AND l.id1 = o.object_id(+)
  AND s.username is NOT Null
  and l.type in ('TX', 'TM')
-- AND o.OBJECT_NAME='MTL_ITEM_LOCATIONS'
and s.sid=&input_sid
order by s.sid,o.OBJECT_NAME;

--4.这是查找被锁包的SQL
   select B.SID,
   b.USERNAME,
   b.MACHINE,
   a.OBJECT,
   'alter system kill session ' || '''' || b.SID || ',' || b.SERIAL# ||
   ''';' kill_command
   FROM V$ACCESS A, V$SESSION B
   WHERE A.SID = B.SID
   and upper(a.OBJECT) like '%CUX%'
   and a.TYPE = 'PACKAGE'
--根据包名查找锁
select 'alter system kill session ' || '''' || to_char(b.sid) || ',' ||
      to_char(b.serial#) || '''' || ';' kill_statement,      
      b.sid,
      b.serial#,
      b.*
 from dba_ddl_locks a, v$session b
where a.session_id = b.sid
  and a.name = 'DFG_EXP_HOMEPG';
=====================================
5.查看等待事件:

select sid,
      event,
      p1     as "p1 as file_id",
      p2     as "p2 as block_id/latch",
      p3     as "p3 as blocks",
      l.name
 from v$session_wait sw, v$latch l
where event not like '%SQL%'
  and event not like '%rdbms%'
  and event not like '%mon%'
  and sw.p2 = l.latch#(+)
  and sid=&input_sid;
==================================================
查看是谁锁了谁
select s1.username /*|| [ email = '@' ] '@' [ / email ] */|| s1.machine || ' ( SID=' || s1.sid || ' ) is blocking '|| s2.username || /*[ email = '@' ] '@' [ / email ] ||*/ s2.machine || ' ( SID=' || s2.sid || ' ) ' ASblocking_status
 from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid = l1.sid
  and s2.sid = l2.sid
  and l1.BLOCK = 1
  and l2.request > 0
  and l1.id1 = l2.id1
  and l2.id2 = l2.id2;








===============================================
6.通过并发管理器的请求的ID查SQL
a.先查sid
SELECT 'Request id: ' || request_id,
      'Trace id: ' || oracle_Process_id,
      'Trace Flag: ' || req.enable_trace,
      'Trace Name: ' || dest.value || '/' || dbnm.value || '_ora_' || oracle_process_id || '.trc',
      'Prog. Name: ' || prog.user_concurrent_program_name,
      'File Name: ' || execname.execution_file_name || execname.subroutine_name,
      'Status : ' || decode(phase_code, 'R', 'Running') || '-' || decode(status_code, 'R', 'Normal'),
      'SID Serial: ' || ses.sid || ',' || ses.serial#,
        ses.sid,
      'Module : ' || ses.module
 from fnd_concurrent_requests    req,
      v$session                  ses,
      v$process                  proc,
      v$parameter                dest,
      v$parameter                dbnm,
      fnd_concurrent_programs_vl prog,
      fnd_executables            execname
where req.request_id = &request id
  and req.oracle_process_id = proc.spid(+)
  and proc.addr = ses.paddr(+)
  and dest.name = 'user_dump_dest'
  and dbnm.name = 'db_name'
  and req.concurrent_program_id = prog.concurrent_program_id
  and req.program_application_id = prog.application_id
     --- and prog.application_id = execname.application_id
  and prog.executable_application_id = execname.application_id
  and prog.executable_id = execname.executable_id;
-------------------------
b.
##查引起等待的类
select a.WAIT_CLASS from v$session a where a.sid= xxx;
-------------------------
c.
##查引起等待的SQL
select sql_text, sql_id, hash_value
 from v$sqltext_with_newlines
where hash_value in
      (select SQL_HASH_VALUE
         from v$session
        where paddr in
              (select addr
                 from v$process
                where spid = (select b.oracle_process_id
                                from apps.fnd_concurrent_requests b
                               where b.request_id = 1413621)))
order by piece;
==========================================================
另一个查并发的
SELECT U.USER_NAME,
      APP.APPLICATION_SHORT_NAME,
      FAT.APPLICATION_NAME,
      FR.RESPONSIBILITY_KEY,
      FRT.RESPONSIBILITY_NAME,
      FFF.FUNCTION_NAME,
      FFT.USER_FUNCTION_NAME,
      ICX.FUNCTION_TYPE,
      ICX.FIRST_CONNECT,
      ICX.LAST_CONNECT
 FROM ICX_SESSIONS          ICX,
      FND_USER              U,
      FND_APPLICATION       APP,
      FND_APPLICATION_TL    FAT,
      FND_RESPONSIBILITY    FR,
      FND_RESPONSIBILITY_TL FRT,
      FND_FORM_FUNCTIONS    FFF,
      FND_FORM_FUNCTIONS_TL FFT
WHERE 1 = 1
  AND U.USER_ID = ICX.USER_ID
  AND ICX.RESPONSIBILITY_APPLICATION_ID = APP.APPLICATION_ID
  AND FAT.APPLICATION_ID = ICX.RESPONSIBILITY_APPLICATION_ID
  AND FAT.LANGUAGE = 'ZHS'
  AND FR.APPLICATION_ID = ICX.RESPONSIBILITY_APPLICATION_ID
  AND FR.RESPONSIBILITY_ID = ICX.RESPONSIBILITY_ID
  AND FRT.LANGUAGE = 'ZHS'
  AND FRT.APPLICATION_ID = ICX.RESPONSIBILITY_APPLICATION_ID
  AND FRT.RESPONSIBILITY_ID = ICX.RESPONSIBILITY_ID
  AND FFF.FUNCTION_ID = ICX.FUNCTION_ID
  AND FFT.FUNCTION_ID = ICX.FUNCTION_ID
  AND ICX.DISABLED_FLAG != 'Y'
  AND ICX.PSEUDO_FLAG = 'N'
  AND (ICX.LAST_CONNECT +
      DECODE(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'),
              NULL,
              ICX.LIMIT_TIME,
              0,
              ICX.LIMIT_TIME,
              FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT') / 60) / 24) >
      SYSDATE
  AND ICX.COUNTER < ICX.LIMIT_CONNECTS;