有关oracle数据的锁定问题

最近老是碰到数据库中的数据表被锁需要解锁的问题，弄了一番后，现在把方法贡献出来给大家，请大家多指教
1、select * from v$locked_object 查出被锁定的对象，其中object_id是对象的ID,session_id是被锁定对象有session ID；
2、select object_name, object_type from dba_objects where object_id = 刚才查出来的object_id;（根据v$locked_object里的object_id提出来的）
这样来查被锁定这个对象的名字，如果能确定是哪个TABLE被锁并且要解锁，则再执行
3、select sid, serial#, machine, program from v$session where sid =第1步中查出来的session_id;(是根据v$locked_object对应锁定记录的session_id找出来的) 然后
4、alter system kill session ‘sid,serial#’;用来杀死这个会话；
以上几个步骤即能解决对象被锁定问题。

另外附上锁定一个表的语句：
-----查锁
SQL> 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;
-----解锁
SQL> ALTER SYSTEM KILL SESSION '10,382';

Oracle数据库表查看锁以及解锁SQL#
//查询被锁的表select A.sid, b.serial#,decode(A.type, 'MR', 'Media Recovery', 'RT','Redo Thread', 'UN','User Name', 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', 'DX', 'Distributed Xaction', 'CF', 'Control File', 'IS', 'Instance State', 'FS', 'File Set', 'IR', 'Instance Recovery', 'ST', 'Disk Space Transaction', 'TS', 'Temp Segment', 'IV', 'Library Cache Invalida-tion', 'LS', 'Log Start or Switch', 'RW', 'Row Wait', 'SQ', 'Sequence Number', 'TE', 'Extend Table', 'TT', 'Temp Table', 'Unknown') LockType,c.object_name,b.username,b.osuser,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.SPIDfrom v$lock a,v$session b,all_objects c,V$PROCESS Dwhere a.sid=b.sid and a.type in ('TM','TX')and c.object_id=a.id1AND B.PADDR=D.ADDR//用于解锁alter system kill session'SID,SERIAL#'
----------------------------------------------------------------------
oracle 解锁表步骤：
1：查看被锁的表


Sql代码
1.SELECT A.OBJECT_NAME, A.STATUS, V.SESSION_ID
2. FROM ALL_OBJECTS A, V$LOCKED_OBJECT V
3.WHERE A.OBJECT_ID = V.OBJECT_ID
SELECT A.OBJECT_NAME, A.STATUS, V.SESSION_ID
FROM ALL_OBJECTS A, V$LOCKED_OBJECT V
WHERE A.OBJECT_ID = V.OBJECT_ID
2:查看锁


Sql代码
1.SELECT A.OBJECT_NAME, A.STATUS, V.SESSION_ID
2. FROM ALL_OBJECTS A, V$LOCKED_OBJECT V
3.WHERE A.OBJECT_ID = V.OBJECT_ID
SELECT A.OBJECT_NAME, A.STATUS, V.SESSION_ID
FROM ALL_OBJECTS A, V$LOCKED_OBJECT V
WHERE A.OBJECT_ID = V.OBJECT_ID 2、杀死锁


Sql代码
1.--alter system kill session 'sid,serial#';
2.alter system kill session '156,87';--查看表大小
----------------------------------------------------------------------
经过测试，下面的两条sql语句能更好的完成锁定记录的查询与解锁问题
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;

---140 78 MSJL PC2011031300NIM\Administrator NC_DELIVETOSAOUT 3
---行id 锁标识 数据库用户 哪一台机器 被锁表 锁模式


----解锁方式,将“行id”和“锁标识”赋给下面的语句即可解锁
alter system kill session '140,78';


----------------------------------------------------------------------