前两天同事又碰到oracle连接n慢的问题，由于这个现象时有时无，于是我检查了下是不是表或者其他对象有被锁住，或者是否有死锁。对象资源被锁是一个经常要碰到的急需解决的问题。
在Oracle里，当前的锁的信息存储在动态性能视图v$lock和v$locked_object中。先来看下它们定义：
Oracle官方文档及简单翻译 (oracle官方可下载资源网址在我的收藏里有列出)：
V$LOCK V$LOCKED_OBJECT V$LOCKED_OBJECT lists all locks acquired by every transaction on the system. It shows which sessions are holding DML locks (that is, TM-type enqueues) on what objects and in what mode.
V$LOCK lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch.
视图V$LOCK列出了当前被Oracle数据库持有的所有的锁以及未完成的锁和栓锁请求。
| Column | Datatype | Description |
|---|---|---|
ADDR | RAW(4 | 8) | Address of lock state object/内存中被锁的对象的地址 |
KADDR | RAW(4 | 8) | Address of lock/内存中锁地址 |
SID | NUMBER | Identifier for session holding or acquiring the lock/持有或申请锁的会话标识号 |
TYPE | VARCHAR2(2) | Type of user or system lock/系统锁或者用户锁类型 The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are:
The locks on the system types are held for extremely short periods of time. The system type locks are listed in Table 8-1 .
|
ID1 | NUMBER | Lock identifier #1 (depends on type) 锁的第1标识号 如果锁的类型是TM，该值表示将要被锁定的对象的标识号； 如果锁的类型是TX，该值表示撤销段号码的十进制值 |
ID2 | NUMBER | Lock identifier #2 (depends on type) 锁的第2标识号。 如果锁的类型是TM，该值为0； 如果锁的类型是TX，该值表示交换次数 |
LMODE | NUMBER | Lock mode in which the session holds the lock: 会话保持的锁的模式
|
REQUEST | NUMBER | Lock mode in which the process requests the lock: 会话申请的锁的模式。与LMODE中的模式相同
|
CTIME | NUMBER | Time since current mode was granted 以秒为单位的，获得当前锁（或转换成当前锁的模式）以来的时间 |
BLOCK | NUMBER | A value of either 0 or 1, depending on whether or not the lock in question is the blocker. 当前锁是否阻塞另一个锁。0＝不阻塞；1＝阻塞 |
视图V$LOCKED_OBJECT列出系统中每个事务获得的所有锁。它列出了那些以具体哪些模式对具体哪些对象持有TM锁的会话。
| Column | Datatype | Description |
|---|---|---|
XIDUSN | NUMBER | Undo segment number/撤销段号码 |
XIDSLOT | NUMBER | Slot number/被锁定的对象在撤销段中的位置 |
XIDSQN | NUMBER | Sequence number/序列号 |
OBJECT_ID | NUMBER | Object ID being locked/被锁定的对象的标识号 |
SESSION_ID | NUMBER | Session ID/ 会话的标识号 |
ORACLE_USERNAME | VARCHAR2(30) | Oracle user name/Oracle用户名 |
OS_USER_NAME | VARCHAR2(30) | OS user name/操作系统用户名 |
PROCESS | VARCHAR2(12) | OS process ID/操作系统进程标识号 |
LOCKED_MODE | NUMBER | Lock mode/ 对象被锁定的模式。 0＝None；1＝Null；2＝Row-S (SS)；3＝Row-X (SX)； 4＝Share；5＝S/Row-X (SSX)；6＝Exclusive |
查找有关锁/死锁的更多我们需要的信息有时还需要通过表all_objects或者视图v$session，它们就不再一一讲解，请到oracle官方文档 查询。
查询有关锁的信息：
查询一：
select a.os_user_name,
a.oracle_username,
a.object_id,
c.object_name,
c.object_type
from v$locked_object a, dba_objects c
where a.object_id=c.object_id;
查询二：
select s.PROCESS,
s.SID,
s.SERIAL#,
b.object_name,
b.subobject_name,
a.LOCKED_MODE,
s.OSUSER,
s.LOGON_TIME,
s.MACHINE,
s.PROGRAM,
s.SQL_ADDRESS,
s.SQL_HASH_VALUE
from v$locked_object a, dba_objects b, v$session s
where a.OBJECT_ID = b.object_id
and a.SESSION_ID = s.SID
找到锁/死锁就好办了。找到相应需要解开的锁可以通过完成该事务或者杀死会话解锁。
杀锁进程可以用以下命令：
alter system kill session ' '查出的SID,查出的SERIAL#'。（这个必须要用查询二来获得相关Session信息）
杀oracle会话进程有时会失效,需要根据SPID到系统中杀相应的oracle进程。另外要注意的是， 我看到网上有外国的学者说， 当直接连接数据库时， 直接用OS命令 $kill process_num 或者 $kill -9 process_num杀系统进程来终止用户连接也不能完全解决问题， 因为一个用户进程可能产生一个以上的锁，杀oracle进程不能彻底解决锁的问题。
Reference:
http://www.lslnet.com/linux/edosc/42/linux-42294950.htm
/
一些ORACLE中的进程被杀掉后，状态被置为"killed"，但是锁定的资源很长时间不释放，有时实在没办法，只好重启数据库。现在提供一种方法解决这种问题，那就是在ORACLE中杀不掉的，在OS一级再杀。
1.下面的语句用来查询哪些对象被锁：
select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id = o.object_id and l.session_id=s.sid;
2.下面的语句用来杀死一个进程：
alter system kill session '24,111'; (其中24,111分别是上面查询出的sid,serial#)
【注】以上两步，可以通过Oracle的管理控制台来执行。
3.如果利用上面的命令杀死一个进程后，进程状态被置为"killed"，但是锁定的资源很长时间没有被释放，那么可以在os一级再杀死相应的进程（线程），首先执行下面的语句获得进程（线程）号：
select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=24 （24是上面的sid）
4.在OS上杀死这个进程（线程）：
1)在unix上，用root身份执行命令:
#kill -9 12345（即第3步查询出的spid）
2)在windows（unix也适用）用orakill杀死线程，orakill是oracle提供的一个可执行命令，语法为：
orakill sid thread
其中：
sid：表示要杀死的进程属于的实例名
thread：是要杀掉的线程号，即第3步查询出的spid。
例：c:>orakill orcl 12345
/
查看当前用户的sid和serial#:
select sid, serial#, status from v$session where audsid=userenv('sessionid');
查看当前用户的spid:
select spid from v$process p, v$session s where s.audsid=userenv('sessionid') and s.paddr=p.addr;
select spid from v$process p join v$session s on p.addr=s.paddr and s.audsid=userenv('sessionid');
查看当前用户的trace file路径:
select p.value || '/' || t.instance || '_ora_' || ltrim(to_char(p.spid,'fm99999')) || '.trc'
from v$process p, v$session s, v$parameter p, v$thread t
where p.addr = s.paddr and s.audsid = userenv('sessionid') and p.name = 'user_dump_dest';
已知spid，查看当前正在执行或最近一次执行的语句:
select /*+ ordered */ sql_text from v$sqltext sql
where (sql.hash_value, sql.address) in (
select decode(sql_hash_value, 0, prev_hash_value, sql_hash_value), decode(sql_hash_value, 0, prev_sql_addr, sql_address)
from v$session s where s.paddr = (select addr from v$process p where p.spid = to_number('&pid')))
order by piece asc;
查看锁和等待:
col user_name format a10
col owner format a10
col object_name format a15
col sid format 999999
col serial# format 999999
col spid format a6
select /*+ rule */ lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username user_name,
o.owner, o.object_name, o.object_type, s.sid, s.serial#, p.spid
from v$locked_object l, dba_objects o, v$session s, v$process p
where l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr
order by o.object_id, xidusn desc;
///
查看锁表进程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;
查看锁表进程SQL语句2：
select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
杀掉锁表进程：
如有記錄則表示有lock，記錄下SID和serial# ，將記錄的ID替換下面的738,1429，即可解除LOCK
alter system kill session '738,1429';
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
