oracle 表锁

1. 重要table及view表及其栏位介绍

select * from dba_objects
object_name,object_id

select * from v$session: 当前session
sid,serial#,machine,prev_sql_addr,username,status,osuser,program,

select * from v$locked_object:  --当前锁
oracle_username,os_user_name,locked_mode,object_id = dba_objects.objects_id,session_id = v$session.sid

SELECT * FROM v$lock;
type(decode(l.type,'TM','TABLE LOCK','TX','ROW LOCK',NULL) ),sid=v$session.sid

SELECT * FROM v$sqlarea; --session对应的sql
address = v$session.prev_sql_addr, sql_text, action

SELECT * FROM v$session_wait;

2.查看锁表进程SQL语句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; 


3.查看锁表进程SQL语句2： 锁对应的session

select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID; 

4.查看锁表进程SQL语句3:session对应锁及object name等信息

SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT Null;

5.杀掉锁表进程： 
如有記錄則表示有lock，記錄下SID和serial# ，將記錄的ID替換下面的738,1429，即可解除LOCK
alter system kill session '738,1429';
PS,Oracle有自己的进程管理策略，很多情况下杀进程往往不能即使生效，可以加alter system kill ... immediately 试试