Oracle中診斷阻塞的session

建立測試環境：

TEST@ PROD> create table t(id int constraint pk_id2 primary key,val int);

TEST@ PROD> select * from t;

        ID        VAL
---------- ----------
         1          1
         2          2


session 1:   
　　檢視SID:               
      
      SQL>  select distinct sid from v$mystat;
      
             SID
      ----------
             125       
      
      update 資料，但不要提交：
               
      TEST@ PROD> update t set val = 3 where id=1;
                                                   
      1 row updated.
        
      
  session 2:
                                                                                
      TEST@ PROD> select distinct sid from v$mystat;
                                              
             SID                                    
      ----------                                    
             141                 
         
         update 資料，操作被阻塞：
            
      TEST@ PROD> update t set val = 4 where id=1;  
      
      
      
 session 3(query):     
 
  　 但有活動事務對物件加鎖的時候,會在v$locked_object檢視中有記錄如object_id,session_id等,
  　　　　 通常被阻塞的session的XIDUSN,XIDSLOT,XIDSQN欄位都為空  ，下圖中session_id為141的是被阻塞的session.
 
      SQL>  select * from v$locked_object;                                                                                                 
                                                                                                                                           
          XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME         PROCESS                  LOCKED_MODE                                  
      ---------- ---------- ---------- ---------- ---------- --------------- -------------------- ------------------------ -----------
               5         21       1141      69926        125 TEST            oracle               32086                              3
               0          0          0      69926        141 TEST            oracle               31089                              3
               
        
        
 
               
     SQL> SELECT a.sid  blocking_sid ,b.sid blocked_sid FROM v$lock a ,v$lock b                    
       2      WHERE b.id1=a.id1 AND a.id2=b.id2                                
       3       AND a.sid<>b.sid                                                
       4      AND b.request<>0;                                                
                                                                          
     BLOCKING_SID BLOCKED_SID                                                  
     ------------ -----------                                                  
              125         141     
        
        
        在透過v$session可以查到session相關的資訊,被阻塞的status一般為ACTIVE,還可以透過sql_address聯合v$sql找到被阻塞的SQL語句.      
     
     SQL>  select sid,serial#,status,sql_address from v$session where sid in(125,141);                                                      
                                                                                 
            SID    SERIAL# STATUS   SQL_ADDRESS                                       
     ---------- ---------- -------- ----------------                                  
            125         27 INACTIVE 00                                                
            141         37 ACTIVE   00000000B8DFDF38    
            
            
    檢視被block 的SQL        
    SQL>  select sql_text from V$sql where address='00000000B8DFDF38';                                                              
                                                                                          
    SQL_TEXT                                                                                  
    ------------------------------------------------------------------------------------------
    update t set val = 4 where id=1