oracle 锁表，锁对象，锁记录

一、oracle锁表

1、查询锁表记录

select sess.sid, 
    sess.serial#, 
    lo.oracle_username, 
    lo.os_user_name, 
    ao.object_name, 
    lo.locked_mode 
    from v$locked_object lo, 
    dba_objects ao, 
    v$session sess 
where ao.object_id = lo.object_id and lo.session_id = sess.sid; 

2、杀死锁表进程

alter system kill session '41,45351'; 

二、oracle锁对象（例如存储过程）

1、查询锁对象的进程

select * FROM dba_ddl_locks where name =upper('SP_SY_WMSQQD_CHECK');

2、杀死锁对象进程

select t.sid, t.serial# from v$session t where t.sid ='867'

alter system kill session '867,30617' immediate;

三、oracle锁记录

1、查询某张表的某条记录被锁，没有提交事务

查看数据库锁,诊断锁的来源及类型： 　　

select object_id,session_id,locked_mode from v$locked_object;

或者用以下命令：

select b.owner,b.object_name,l.session_id,l.locked_mode from v$locked_object l, dba_objects b where b.object_id=l.object_id

2、找出数据库的serial#,以备杀死： 　　

 select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;

3、杀死该session alter system kill session 'sid,serial#'