Oracle 锁模式以及解锁

--unlock dml lock

select /*+rule*/ distinct 'alter system kill session '||''''||s.sid||','||s.SERIAL#||',@'||s.inst_id||''''||' immediate;' as kill_session_sql,o.owner||'.'||o.object_name as locked_object,l.locked_mode,s.USERNAME,s.TERMINAL,s.MODULE,s.MACHINE,sl.SQL_TEXT  from gv$locked_object l,gv$session s,dba_objects o ,gv$sqlarea sl

where l.SESSION_ID=s.SID and l.OBJECT_ID=o.object_id and s.SQL_ID=sl.SQL_ID

--unlock ddl lock

select /*+rule*/distinct 'alter system kill session '||''''||s.sid||','||s.SERIAL#||',@'||s.inst_id||''''||' immediate;' as kill_session_sql, l.owner||'.'||l.name as locked_object,s.USERNAME,s.TERMINAL,s.MODULE,s.MACHINE,sl.SQL_TEXT,l.mode_held  from  dba_ddl_locks l,gv$session s ,gv$sqlarea sl

where l.type='Table/Procedure/Type' and l.mode_held in ('Share','Exclude') and l.SESSION_ID=s.SID   and s.SQL_ID=sl.SQL_ID

--unlock temporary table lock

select /*+rule*/ distinct 'alter system kill session '||''''||s.sid||','||s.SERIAL#||',@'||s.inst_id||''''||' immediate;' as kill_session_sql, s.USERNAME,s.TERMINAL,s.MODULE,s.MACHINE,sl.SQL_TEXT,l.LMODE,o.owner||'.'||o.object_name as object_name from gv$session s ,gV$LOCK l,dba_objects o,gv$sqlarea sl

where l.type='TO' and l.sid=s.sid  and o.object_id=l.id1

and s.sql_id=sl.sql_id;

lock mode 解释

0             表级锁

None       select

1              可中断解析锁

Null         sql/plsql 维持有效性

2              表级锁

Sub-Share      (create index online,LOCK TABLE tab1 IN ROW SHARE MODE NOWAIT)

3              表级锁

Sub-Exclusive  阻止其他ddl操作(select for update/insert/delete/update,LOCK TABLE tab1 IN ROW EXCLUSIVE MODE NOWAIT)

4              表级锁

Share          阻止其他dml、ddl操作(create index,LOCK TABLE tab1 IN SHARE  MODE NOWAIT )     

5              表级锁

Share/Sub-Exclusive  阻止其他事物操作(LOCK TABLE tab1 IN SHARE ROW EXCLUSIVE MODE NOWAIT)

6              表级锁/行锁

Exclusive      独立访问(Alter table, Drop table, Drop Index, Truncate table,LOCK TABLE tab1 IN EXCLUSIVE MODE NOWAIT)