25
07年9月
oracel表简单解锁方法
贝贝爸 发表在 技术文章 |有时候操作数据库会被锁掉，表现就是对表操作的时候没有任何反应。
解锁的方法如下。重复下面的三个sql
SQL> select sid from v$lock where block=1;
SID
----------
20
SQL> select sid,serial# from v$session where sid=20;
SID SERIAL#
---------- ----------
20 2649
SQL> alter system kill session '20,2649';
System altered.
SID
----------
20
SQL> select sid,serial# from v$session where sid=20;
SID SERIAL#
---------- ----------
20 2649
SQL> alter system kill session '20,2649';
System altered.
只到没有block=1的sid
SQL> select sid from v$lock where block=1;
no rows selected
no rows selected
感谢wxx
另一个查锁的sql
select l.sid,
s.username,
decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
o.owner,
o.object_name,
o.object_type,
s.terminal,
s.machine,
s.program,
s.osuser
from v$session s, v$lock l, dba_objects o
where s.sid = l.sid
and o.object_id = l.id1
and s.username is not null
s.username,
decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
o.owner,
o.object_name,
o.object_type,
s.terminal,
s.machine,
s.program,
s.osuser
from v$session s, v$lock l, dba_objects o
where s.sid = l.sid
and o.object_id = l.id1
and s.username is not null
