当前位置:首页 > 开发 > 数据库 > 正文

oracle查询锁表解锁语句

发表于: 2014-03-11   作者:bijian1013   来源:转载   浏览:
摘要: 一.查询锁定的表 如下语句,都可以查询锁定的表 语句一: select a.sid, a.serial#, p.spid, c.object_name, b.session_id, b.oracle_username, b.os_user_name from v$process p, v$s

一.查询锁定的表

如下语句,都可以查询锁定的表

语句一:

select a.sid,
       a.serial#,
       p.spid,
       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
   and c.OBJECT_name = 'TABLE_NAME';

语句二:

SELECT s.sid, s.serial#, o.object_name, s.machine
  FROM gv$locked_object l, dba_objects o, gv$session s
 WHERE l.object_id  = o.object_id
   AND l.session_id = s.sid
   AND o.object_name = 'TABLE_NAME';

语句三:

SELECT l.session_id sid,
       s.serial#,
       l.locked_mode,
       l.oracle_username,
       l.os_user_name,
       s.machine,
       s.terminal,
       o.object_name,
       s.logon_time
  FROM v$locked_object l, all_objects o, v$session s
 WHERE l.object_id = o.object_id
   AND l.session_id = s.sid
   AND o.object_name = 'TABLE_NAME'
 ORDER BY sid, s.serial#;

语句四:

select a.sid,
       b.serial#,
       c.object_name,
       decode(a.lmode,
              0,
              'None',
              1,
              'Null',
              2,
              'Row-S',
              3,
              'Row-X',
              4,
              'Share',
              5,
              'S/Row-X',
              6,
              'Exclusive',
              'Unknown') LockMode,
       B.MACHINE,
       D.SPID,
       b.PROGRAM
  from v$lock a, v$session b, all_objects c, V$PROCESS D
 where a.sid = b.sid
   and a.type in ('TM', 'TX')
   and c.object_id = a.id1
   and B.PADDR = D.ADDR
   and c.object_name = 'TABLE_NAME';

 

二.kill掉锁住的进程,解锁

--释放SESSION SQL
alter system kill session 'SID,serial#';

oracle查询锁表解锁语句

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
最近在工作中同事们经常遇到锁表、误删表和程序覆盖的情况,现总结下遇到这三种情况的解决方案: 1.
查看哪些表被锁和怎样解锁 查看那些表被锁: select sess.sid,sess.serial#, lo.oracle_username,lo
SELECT sid, serial#, username, osuser FROM v$session where sid in (select session_id from v$l
1.相关表字段 USER_COL_COMMENTS 字段基本信息表 USER_TAB_COLUMNS 字段详情表 SELECT T.TABLE_NAME
基本查询 查看表结构 SQL> desc tableName; 查询所有列 SQL> select * from tableName; 6.5W
解锁:alter system kill session '52,23'; 查询解锁条件:SELECT * FROM (SELECT /*+ rule */ s.us
一:用户之间表的访问: 例如:linuxidc用户想访问system用户中的users表,命令如下 : grant selec
oracle数据库的权限系统分为系统权限与对象权限。系统权限( database system privilege )可以让用户
oracle数据库的权限系统分为系统权限与对象权限。系统权限( database system privilege )可以让用户
各种oracle参数查询语句 1、show parameter;--显示各个系统参数配置 2、select * from v$parameter
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号