快速查出Oracle數據庫中鎖等待的方法，查出oracle數據庫

快速查出Oracle數據庫中鎖等待的方法，查出oracle數據庫

通常在大型數據庫系統中，為了保證數據的一致性，在對數據庫中的數據進行操作時，系統會進行對數據相應的鎖定。

這些鎖定中有"只讀鎖"、"排它鎖"，"共享排它鎖"等多種類型，而且每種類型又有"行級鎖"（一次鎖住一條記錄），"頁級鎖"（一次鎖住一頁，即數據庫中存儲記錄的最小可分配單元），"表級鎖"（鎖住整個表）。若為"行級排它鎖"，則除被鎖住的該行外，該表中其它行均可被其它的用戶進行修改(Update)或刪除(delete)操作，若為"表級排它鎖"，則所有其它用戶只能對該表進行查詢(select)操作，而無法對其中的任何記錄進行修改或刪除。當程序對所做的修改進行提交(commit)或回滾後(rollback)後，鎖住的資源便會得到釋放，從而允許其它用戶進行操作。

但是在某些情況下，由於程序中的一些特殊原因，鎖住資源後長時間未對其工作進行提交；或是由於用戶的原因，如調出需要修改的數據後，未及時修改並提交，而是放置於一旁；或是由於客戶服務器方式中客戶端出現"死機"，而服務器端卻並未檢測到，從而造成鎖定的資源未被及時釋放，最終出現影響到其它用戶操作的情況。

因而，如何迅速地診斷出鎖住資源的用戶以及解決其鎖定便是數據庫管理員的一個挑戰。
由於數據庫應用系統越來越復雜， 一旦出現由於鎖資源未及時釋放的情況，便會引起對一相同表進行操作的大量用戶無法進行操作，從而影響到系統的使用。此時，DBA應盡量快地解決問題。但是，由於在Oracle 8.0.x 中執行"獲取正在等待鎖資源的用戶名"的查詢語句

select a.username, a.sid, a.serial#, b.id1
 from v$session a, v$lock b
 where a.lockwait = b.kaddr

十分緩慢，（在 Oracle 7.3.4中執行很快)，而且，執行"查找阻塞其它用戶的用戶進程"的查詢語句

  select a.username, a.sid, a.serial#, b.id1
   from v$session a, v$lock b
   where b.id1 in
      (select distinct e.id1
       from v$session d, v$lock e
       where d.lockwait = e.kaddr)
    and a.sid = b.sid
    and b.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 表,並在查詢要用到的字段上創建索引，以加快查詢速度

drop table my_session;
create table my_session
as
select a.username, a.sid, a.serial#,
a.lockwait, a.machine,a.status,
a.last_call_et,a.sql_hash_value,a.program
 from v$session a
 where 1=2 ;

create unique index my_session_u1 on my_session(sid);
create index my_session_n2 on my_session(lockwait);
create index my_session_n3 on my_session(sql_hash_value);

rem 從 v$lock 視圖中取出字段，創建 my_lock 表，並在查詢要用到的字段上創建索引，以加快查詢速度

drop table my_lock;
create table my_lock
as
select id1, kaddr, sid, request,type
 from v$lock
 where 1=2;

create index my_lock_n1 on my_lock(sid);
create index my_lock_n2 on my_lock(kaddr);

rem 從 v$sqltext 視圖中取出字段，創建 my_sqltext 表，並在查詢要用到的字段上創建索引，以加快查詢速度

drop table my_sqltext;
create table my_sqltext
as
select hash_value , sql_text
 from v$sqltext
 where 1=2;

create index my_sqltext_n1 on my_sqltext ( hash_value);

然後，創建一個 SQL 腳本文件，以便需要時可從 SQL*Plus 中直接調用。其中，首先用 truncate table 表名命令將表中的記錄刪除。之所以用 truncate 命令，而不是用delete 命令，是因為delete 命令執行時，將會產生重演記錄，速度較慢，而且索引所占的空間並未真正釋放，若反復做 insert及delete,則索引所占的空間會不斷增長，查詢速度也會變慢。而 truncate命令不產生重演記錄，速度執行較delete快，而且索引空間被相應地釋放出來。刪除記錄後，再將三個視圖中的相關記錄插入自己創建的三個表中。最後，對其進行查詢，由於有索引，同時由於在插入時條件過濾後，記錄數相對來說較少，因而查詢速度很快，馬上可以看到其結果。

此時，若發現該阻塞其它用戶進程的進程是正常操作中，則可通知該用戶對其進行提交，從而達到釋放鎖資源的目的；若為未正常操作，即，其狀態為"inactive"，且其last_call_et已為較多長時間，則可執行以下語句將該進程進行清除，系統會自動對其進行回滾，從而釋放鎖住的資源。

alter system kill session 'sid, serial#'; 

SQL 腳本如下：

set echo off
set feedback off
prompt '刪除舊記錄.....'
truncate table my_session;
truncate table my_lock;
truncate table my_sqltext;

prompt '獲取數據.....'
insert into my_session
select a.username, a.sid, a.serial#,
    a.lockwait, a.machine,a.status,
    a.last_call_et,a.sql_hash_value,a.program
 from v$session a
 where nvl(a.username,'NULL')< >'NULL;

insert into my_lock
select id1, kaddr, sid, request,type
 from v$lock;

insert into my_sqltext
select hash_value , sql_text
 from v$sqltext s, my_session m
 where s.hash_value=m.sql_hash_value;

column username format a10
column machine format a15
column last_call_et format 99999 heading "Seconds"
column sid format 9999

prompt "正在等待別人的用戶"
select a.sid, a.serial#, 
a.machine,a.last_call_et, a.username, b.id1
 from my_session a, my_lock b
 where a.lockwait = b.kaddr;

prompt "被等待的用戶"
select a.sid, a.serial#, 
a. machine, a.last_call_et,a.username,
b. b.type,a.status,b.id1
 from my_session a, my_lock b
 where b.id1 in
   (select distinct e.id1
     from my_session d, my_lock e
    where d.lockwait = e.kaddr)
  and a.sid = b.sid
  and b.request=0;

prompt "查出其 sql "
select a.username, a.sid, a.serial#,
 b.id1, b.type, c.sql_text
 from my_session a, my_lock b, my_sqltext c
 where b.id1 in
    (select distinct e.id1
     from my_session d, my_lock e
     where d.lockwait = e.kaddr)
  and a.sid = b.sid
  and b.request=0
  and c.hash_value =a.sql_hash_value;
 

以上思路也可用於其它大型數據庫系統如 Informix, Sybase,DB2中。通過使用該腳本，可以極大地提高獲取系統中當前鎖等待的情況，從而及時解決數據庫應用系統中的鎖等待問題。而且，由於實際上已取出其 program 名及相應的 sql 語句，故可以在事後將其記錄下來，交給其開發人員進行分析並從根本上得到解決。

ORACLE數據庫怎查看死鎖

SELECT substr(v$lock.sid,1,4) "SID",
substr(username,1,12) "UserName",
substr(object_name,1,25) "ObjectName",
v$lock.type "LockType",
decode(rtrim(substr(lmode,1,4)),
'2','Row-S (SS)','3','Row-X (SX)',
'4','Share', '5','S/Row-X (SSX)',
'6','Exclusive', 'Other' ) "LockMode",
substr(v$session.program,1,25) "ProgramName"
FROM V$LOCK,SYS.DBA_OBJECTS,V$SESSION
WHERE (OBJECT_ID = v$lock.id1
AND v$lock.sid = v$session.sid
AND username IS NOT NULL
AND username NOT IN ('SYS','SYSTEM')
AND SERIAL# != 1);
 

Oracle數據庫，怎解除數據庫用戶賬戶的鎖定？？怎更改口令？？

D:\oracle\ora92\bin>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 8月 16 11:32:22 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn /as sysdba
已連接。
SQL> alter user system identified by password;

用戶已更改。

SQL> alter user sys identified by password;

用戶已更改。

SQL> alter user system identified by manger;

用戶已更改。

SQL> exit
從Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production中斷開

D:\oracle\ora92\bin>sqlplus

SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 8月 16 11:40:37 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

請輸入用戶名: system
請輸入口令:

連接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
參考資料：lanxing.javaeye.com/blog/112999