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 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 試試