查询oracle数据库锁

㈠ 查看Oracle中是否有锁表的sql

1、查看是否有锁表的sql
代码如下:
select 'blocker('||lb.sid||':'||sb.username||')-sql:'|| qb.sql_text blockers,
'waiter ('||lw.sid||':'||sw.username||')-sql:'|| qw.sql_text waiters
from v$lock lb,
v$lock lw,
v$session sb,
v$session sw,
v$sql qb,
v$sql qw
where lb.sid=sb.sid
and lw.sid=sw.sid
and sb.prev_sql_addr=qb.address
and sw.sql_address=qw.address
and lb.id1=lw.id1
and sw.lockwait is not null
and sb.lockwait is null
and lb.block=1 ;
2、查看被锁的表
代码如下:
select p.spid,a.serial#,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 ;
3、查看那个用户那个进程造成死锁，锁的级别
代码如下:
select b.owner,b.object_name,l.session_id,l.locked_mode fromv$locked_object l, dba_objects
4、查看连接的进程
代码如下:
SELECT sid, serial#, username, osuser FROMv$session;
5、查看是哪个session引起的
代码如下:
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;
6、杀掉进程
代码如下:
alter system kill session 'sid,serial#';

㈡ Oracle 锁表情况查询sql

select LOCK_INFO.OWNER || '.' || LOCK_INFO.OBJ_NAME as "已锁物件名称", --物件名称(已经被锁住)

LOCK_INFO.SUBOBJ_NAME as "已锁子物件名称", -- 子物件名称(已经被锁住)

SESS_INFO.MACHINE as "机器名称", -- 机器名称

LOCK_INFO.SESSION_ID as "会话ID", -- 会话SESSION_ID

SESS_INFO.SERIAL# as "会话SERIAL#", -- 会话SERIAL#

SESS_INFO.SPID as "OS系统的SPID", -- OS系统的SPID

(SELECT INSTANCE_NAME FROM V$INSTANCE) "实例名SID", --实例名SID

LOCK_INFO.ORA_USERNAME as "ORACLE用户", -- ORACLE系统用户名称

LOCK_INFO.OS_USERNAME as "OS用户", -- 作业系统用户名称

LOCK_INFO.PROCESS as "进程编号", -- 进程编号

LOCK_INFO.OBJ_ID as "对象ID", -- 对象ID

LOCK_INFO.OBJ_TYPE as "对象类型", -- 对象类型

SESS_INFO.LOGON_TIME as "登录时间", -- 登录时间

SESS_INFO.PROGRAM as "程式名称", -- 程式名称

SESS_INFO.STATUS as "会话状态", -- 会话状态

SESS_INFO.LOCKWAIT as "等待锁", -- 等待锁

SESS_INFO.ACTION as "动作", -- 动作

SESS_INFO.CLIENT_INFO as "客户资讯" -- 客户资讯

from (select obj.OWNER as OWNER,

obj.OBJECT_NAME as OBJ_NAME,

obj.SUBOBJECT_NAME as SUBOBJ_NAME,

obj.OBJECT_ID as OBJ_ID,

obj.OBJECT_TYPE as OBJ_TYPE,

lock_obj.SESSION_ID as SESSION_ID,

lock_obj.ORACLE_USERNAME as ORA_USERNAME,

lock_obj.OS_USER_NAME as OS_USERNAME,

lock_obj.PROCESS as PROCESS

from (select *

from all_objects

where object_id in (select object_id from v$locked_object)) obj,

v$locked_object lock_obj

where obj.object_id = lock_obj.object_id) LOCK_INFO,

(select SID,

SERIAL#,

LOCKWAIT,

STATUS,

(select spid from v$process where addr = a.paddr) spid,

PROGRAM,

ACTION,

CLIENT_INFO,

LOGON_TIME,

MACHINE

from v$session a) SESS_INFO

where LOCK_INFO.SESSION_ID = SESS_INFO.SID

order by LOCK_INFO.SESSION_ID;

㈢ 查询oracle 数据库里有哪些表锁死

用如下语句查询锁死的表：

selectp.spid,
a.serial#,
c.object_name,
b.session_id,
b.oracle_username,
b.os_user_name
fromv$processp,v$sessiona,v$locked_objectb,all_objectsc
wherep.addr=a.paddr
anda.process=b.process
andc.object_id=b.object_id;

其中object_name就是被锁的表名，如图：