快速查出Oracle数据库中锁等待的方法
通常在大型数据库系统中，为了保证数据的一致性，在对数据库中的数据进行操作时，系统会进行对数据相应的锁定。
这些锁定中有"只读锁"、"排它锁"，"共享排它锁"等多种类型，而且每种类型又有"行级锁"（一次锁住一条记录），"页级锁"（一次锁住一页，即数据库中存储记录的最小可分配单元），"表级锁"（锁住整个表）。若为"行级排它锁"，则除被锁住的该行外，该表中其它行均可被其它的用户进行修改(Update)或删除(delete)操作，若为"表级排它锁"，则所有其它用户只能对该表进行查询(select)操作，而无法对其中的任何记录进行修改或删除。当程序对所做的修改进行提交(commit)或回滚后(rollback)后，锁住的资源便会得到释放，从而允许其它用户进行操作。
但是在某些情况下，由于程序中的一些特殊原因，锁住资源后长时间未对其工作进行提交；或是由于用户的原因，如调出需要修改的数据后，未及时修改并提交，而是放置于一旁；或是由于客户服务器方式中客户端出现"死机"，而服务器端却并未检测到，从而造成锁定的资源未被及时释放，最终出现影响到其它用户操作的情况。
因而，如何迅速地诊断出锁住资源的用户以及解决其锁定便是数据库管理员的一个挑战。
由于数据库应用系统越来越复杂，一旦出现由于锁资源未及时释放的情况，便会引起对一相同表进行操作的大量用户无法进行操作，从而影响到系统的使用。此时，DBA应尽量快地解决问题。但是，由于在Oracle8.0.x中执行"获取正在等待锁资源的用户名"的查询语句
selecta.username,a.sid,a.serial#,b.id1 fromv$sessiona,v$lockb wherea.lockwait=b.kaddr
十分缓慢，（在Oracle7.3.4中执行很快)，而且，执行"查找阻塞其它用户的用户进程"的查询语句
selecta.username,a.sid,a.serial#,b.id1 fromv$sessiona,v$lockb whereb.id1in (selectdistincte.id1 fromv$sessiond,v$locke whered.lockwait=e.kaddr) anda.sid=b.sid andb.request=0
执行得也十分缓慢。因而，往往只好通过将v$session中状态为"inactive"（不活动）并且最后一次进行操作时间至当前已超过20分钟以上(last_call_et>20*60秒)的用户进程清除，然后才使得问题得到解决。
但是，这种一刀切的方法实际上是"把婴儿与脏水一起泼掉"。因为，有些用户的进程尽管也为"inactive"，并且也已有较长时间未活动，但是，那是由于他们处于锁等待状态。
因而，笔者想到了一个解决办法。即通过将问题发生时的v$lock，v$session视图中的相关记录保存于自己建立的表中，再对该表进行查询，则速度大大提高，可以迅速发现问题。经实际使用，效果非常好。在接到用户反映后，几秒钟即可查出由于锁住资源而影响其它用户的进程，并进行相应的处理。
首先，以dba身份（不一定为system）登录入数据库中，创建三个基本表:my_session，my_lock,my_sqltext，并在将会进行查询的列上建立相应的索引。语句如下：
rem从v$session视图中取出关心的字段，创建my_session表,并在查询要用到的字段上创建索引，以加快查询速度
droptablemy_session; createtablemy_session as selecta.username,a.sid,a.serial#, a.lockwait,a.machine,a.status, a.last_call_et,a.sql_hash_value,a.program fromv$sessiona where1=2; createuniqueindexmy_session_u1onmy_session(sid); createindexmy_session_n2onmy_session(lockwait); createindexmy_session_n3onmy_session(sql_hash_value);
rem从v$lock视图中取出字段，创建my_lock表，并在查询要用到的字段上创建索引，以加快查询速度
droptablemy_lock; createtablemy_lock as selectid1,kaddr,sid,request,type fromv$lock where1=2; createindexmy_lock_n1onmy_lock(sid); createindexmy_lock_n2onmy_lock(kaddr);
rem从v$sqltext视图中取出字段，创建my_sqltext表，并在查询要用到的字段上创建索引，以加快查询速度
droptablemy_sqltext; createtablemy_sqltext as selecthash_value,sql_text fromv$sqltext where1=2; createindexmy_sqltext_n1onmy_sqltext(hash_value);
然后，创建一个SQL脚本文件，以便需要时可从SQL*Plus中直接调用。其中，首先用truncatetable表名命令将表中的记录删除。之所以用truncate命令，而不是用delete命令，是因为delete命令执行时，将会产生重演记录，速度较慢，而且索引所占的空间并未真正释放，若反复做insert及delete,则索引所占的空间会不断增长，查询速度也会变慢。而truncate命令不产生重演记录，速度执行较delete快，而且索引空间被相应地释放出来。删除记录后，再将三个视图中的相关记录插入自己创建的三个表中。最后，对其进行查询，由于有索引，同时由于在插入时条件过滤后，记录数相对来说较少，因而查询速度很快，马上可以看到其结果。
此时，若发现该阻塞其它用户进程的进程是正常操作中，则可通知该用户对其进行提交，从而达到释放锁资源的目的；若为未正常操作，即，其状态为"inactive"，且其last_call_et已为较多长时间，则可执行以下语句将该进程进行清除，系统会自动对其进行回滚，从而释放锁住的资源。
altersystemkillsession'sid,serial#';
SQL脚本如下：
setechooff setfeedbackoff prompt'删除旧记录.....' truncatetablemy_session; truncatetablemy_lock; truncatetablemy_sqltext; prompt'获取数据.....' insertintomy_session selecta.username,a.sid,a.serial#, a.lockwait,a.machine,a.status, a.last_call_et,a.sql_hash_value,a.program fromv$sessiona wherenvl(a.username,'NULL')<>'NULL; insertintomy_lock selectid1,kaddr,sid,request,type fromv$lock; insertintomy_sqltext selecthash_value,sql_text fromv$sqltexts,my_sessionm wheres.hash_value=m.sql_hash_value; columnusernameformata10 columnmachineformata15 columnlast_call_etformat99999heading"Seconds" columnsidformat9999 prompt"正在等待别人的用户" selecta.sid,a.serial#, a.machine,a.last_call_et,a.username,b.id1 frommy_sessiona,my_lockb wherea.lockwait=b.kaddr; prompt"被等待的用户" selecta.sid,a.serial#, a.machine,a.last_call_et,a.username, b.b.type,a.status,b.id1 frommy_sessiona,my_lockb whereb.id1in (selectdistincte.id1 frommy_sessiond,my_locke whered.lockwait=e.kaddr) anda.sid=b.sid andb.request=0; prompt"查出其sql" selecta.username,a.sid,a.serial#, b.id1,b.type,c.sql_text frommy_sessiona,my_lockb,my_sqltextc whereb.id1in (selectdistincte.id1 frommy_sessiond,my_locke whered.lockwait=e.kaddr) anda.sid=b.sid andb.request=0 andc.hash_value=a.sql_hash_value;
以上思路也可用于其它大型数据库系统如Informix,Sybase,DB2中。通过使用该脚本，可以极大地提高获取系统中当前锁等待的情况，从而及时解决数据库应用系统中的锁等待问题。而且，由于实际上已取出其program名及相应的sql语句，故可以在事后将其记录下来，交给其开发人员进行分析并从根本上得到解决。