oracle怎么样看哪个表被锁

栏目:eas cloud知识作者:金蝶来源:金蝶云社区发布:2024-09-22浏览:1

oracle怎么样看哪个表被锁

如果数据库有锁,想查看是哪个表被锁,如何查?

可以用下面语句,找到被锁的表、操作进程号,会话ID

Set line 200

column spid format a20

 column OWNER format a16

 column OBJECT_NAME  format a20

column ORACLE_USERNAME format a16

column OS_USER_NAME format a16

select p.spid,a.serial#,c.OWNER ,c.object_name,b.session_id,b.oracle_username,b.os_user_name

from gv$process p,gv$session a, gv$locked_object b,all_objects c

where p.addr=a.paddr and a.process=b.process and c.object_id=b.object_id ;


如果想看是哪些阻塞的详细信息,还可以下面语句:


SELECT (   '节点 '
          || a.inst_id
          || ' session '
          || a.sid
          || ','
          || a_s.serial#
          || ' 阻塞了 节点 '
          || b.inst_id
          || ' session '
          || b.sid
          || ','
          || b_s.serial#)
            blockinfo,
         a.inst_id,
         a_s.sid,
         a_s.schemaname,
         a_s.module,
         a_s.status,
         a.TYPE lock_type,
         a.id1,
         a.id2,
         DECODE (a.lmode,
                 0, 'none',
                 1, NULL,
                 2, 'row-S (SS)',
                 3, 'row-X (SX)',
                 4, 'share (S)',
                 5, 'S/Row-X (SSX)',
                 6, 'exclusive (X)')
            lock_mode,
         '后为被阻塞信息' remark_flag,
         b.inst_id blocked_inst_id,
         b_s.sid blocked_sid,
         b.TYPE blocked_lock_type,
         DECODE (b.request,
                 0, 'none',
                 1, NULL,
                 2, 'row-S (SS)',
                 3, 'row-X (SX)',
                 4, 'share (S)',
                 5, 'S/Row-X (SSX)',
                 6, 'exclusive (X)')
            blocked_lock_request,
         b_s.schemaname blocked_schemaname,
         b_s.module blocked_module,
         b_s.status blocked_status,
         b_s.sql_id blocked_sql_id,
         obj.owner blocked_owner,
         obj.object_name blocked_object_name,
         obj.object_type blocked_object_type,
         CASE
            WHEN b_s.row_wait_obj# <> -1
            THEN
               DBMS_ROWID.rowid_create (1,
                                        obj.data_object_id,
                                        b_s.row_wait_file#,
                                        b_s.row_wait_block#,
                                        b_s.row_wait_row#)
            ELSE
               '-1'
         END
            blocked_rowid,  --被阻塞数据的rowid
         DECODE (obj.object_type,
                 'TABLE',    'select * from '
                          || obj.owner
                          || '.'
                          || obj.object_name
                          || ' where rowid='''
                          || DBMS_ROWID.rowid_create (1,
                                                      obj.data_object_id,
                                                      b_s.row_wait_file#,
                                                      b_s.row_wait_block#,
                                                      b_s.row_wait_row#)
                          || '''',
                 NULL)
            blocked_data_querysql
    FROM gv$lock a,
         gv$lock b,
         gv$session a_s,
         gv$session b_s,
         dba_objects obj
   WHERE     a.id1 = b.id1
         AND a.id2 = b.id2
         AND a.block > 0    --阻塞了其他人
         AND b.request > 0
         AND (   (a.inst_id = b.inst_id AND a.sid <> b.sid)
              OR (a.inst_id <> b.inst_id))
         AND a.sid = a_s.sid
         AND a.inst_id = a_s.inst_id
         AND b.sid = b_s.sid
         AND b.inst_id = b_s.inst_id
         AND b_s.row_wait_obj# = obj.object_id(+)
ORDER BY a.inst_id, a.sid;

oracle怎么样看哪个表被锁

如果数据库有锁,想查看是哪个表被锁,如何查?可以用下面语句,找到被锁的表、操作进程号,会话IDSet line 200column spid format a2...
点击下载文档
确认删除?
回到顶部
客服QQ
  • 客服QQ点击这里给我发消息