oracle decode_錯過血虧！一文搞懂Oracle鎖相關視圖及相關操作

本文主要研究鎖的相關視圖，以及鎖的相關操作，通過視圖查鎖的問題。

一、v$transaction視圖

第一個視圖是v$transaction，就是Oracle數據庫所有活動的事務數，所有活動的事務每一個活動的事務在這里有一行。

v$transaction

XIDUSN表示當前事務使用的回滾段的編號

XIDSLOT說明該事務在回滾段頭部的事務表中對應的記錄編號（也可以叫做槽號）

XIDSQN說明序列號

STATUS說明該事務是否為活動的

這是v$transaction視圖的結構，這里做一個實驗

在一個hr用戶的sqlplus中執行

SQL> delete from employees where rownum=1;
1 row deleted.

我刪一行，開始一個事務，以管理員身份運行，去執行一下

select xidusn,xidslot,xidsqn,status from v$transaction;

看看有幾個事務

結果：

SQL> select xidusn,xidslot,xidsqn,status from v$transaction; XIDUSN XIDSLOT XIDSQN STATUS---------- ---------- ---------- ---------------- 4 43 216 ACTIVE

一個事務在這里面有一行

XIDUSN是事務使用的回滾段的編號XIDSLOT是哪個槽位數XIDSQN是覆蓋多少次這三個唯一的標示一個事務的編號

STATUS是當前事務的狀態，這個事務為ACTIVE；這是v$transaction，所有的活動事務里面都有

二、v$lock視圖

v$lock 記錄了session已經獲得的鎖定以及正在請求的鎖定的信息 SID說明session的ID號 TYPE說明鎖的類型，主要關注TX和TM LMODE說明已經獲得的鎖定的模式，以數字編碼表示 REQUEST說明正在請求的鎖定的模式，以數字編碼表示 BLOCK說明是否阻止了其他用戶獲得鎖定，大于0說明是，等于0說明否

v$lock這里面，記錄了session已經獲得的鎖定以及正在請求的鎖定的信息，就是每個會話，它已經獲取的鎖和正在申請的鎖它都會列出來

上面執行了

delete from employees where rownum=1;

一個事務開始以后至少產生幾個鎖，第一個行上加鎖了，行上的鎖你是看不見的，因為它在行上，但是我們開始一個事務有一個事務鎖，同時在表上應該加了個RX鎖，應該這時候有兩個鎖，一個TX鎖事務鎖，一個是TM級別上的表級的RX鎖。

使用語句

select sid,type,id1,id2, decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode, decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,block from v$lock where sid=129;

這里把它取名為(#A)語句，最后where條件有sid，sid是會話的編號

先查一下我們會話的編號是多少，在我的實驗環境下，在hr用戶的sqlplus中

SQL> select sid from v$mystat where rownum=1;select sid from v$mystat where rownum=1 *ERROR at line 1:ORA-00942: table or view does not exist

hr用戶無權訪問v$mystat，所以換個方法，使用下面的命令得到hr用戶當前的sid

SQL> select userenv('sid') from dual;
USERENV('SID')-------------- 132

得到SID是132

然后用132替換前面(#A)語句where條件下的sid的值，然后在管理員用戶下查一下，因為hr用戶依然無法訪問v$lock。

執行結果

SQL> select sid,type,id1,id2,decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,block 2 3 from v$lock 4 where sid=132;
SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK---------- -- ---------- ---------- ------------------- ------------------- ---------- 132 TM 51852 0 Row share None 0 132 TM 51855 0 Row share None 0 132 TM 51857 0 Row Exclusive None 0 132 TM 51864 0 Row share None 0 132 TM 51871 0 Row share None 0 132 TM 51889 0 Row share None 0 132 TM 51894 0 Row share None 0 132 TM 51902 0 Row share None 0 132 TX 262187 216 Exclusive None 0
9 rows selected.

我們看一下132這個會話在很多的表上產生了TM鎖，132這個會話至少底下產生了一個TX鎖，同時132產生了TM鎖，LOCK_MODE中是Row share說明是RS鎖，是select for update產生的鎖；132這個會話產生的TM鎖的ID1列的ID數，這個TM在某個表上產生的鎖，ID1就是這個表的編號

有一個是51902，我們可以根據51902查出來

select object_name from dba_objects where object_id=51902;

查出51902是哪個表

執行結果

SQL> select object_name from dba_objects where object_id=51902;
OBJECT_NAME-------------------------------------------------------------------------------------------------------PRODUCT_INFORMATION

對象編號51902是PRODUCT_INFORMATION表，說明我們找錯了，這個表上加的鎖是Row share類型的鎖刪除操作的表應該產生Row Exclusive類型的鎖，前面delete語句刪除EMPLOYEES表中行時牽涉到了PRODUCT_INFORMATION這個表，是主外鍵約束關系的原因在PRODUCT_INFORMATION表產生了RS鎖。

再看剛才的結果

132 TM 51857 0 Row Exclusive None 0

鎖應該是RX鎖，TM級別的RX鎖，是51857

把語句改為

select object_name from dba_objects where object_id=51857;

改一下，執行一下，看一下

SQL> select object_name from dba_objects where object_id=51857;
OBJECT_NAME----------------------------------------------------------------------------------------------------EMPLOYEES

結果是EMPLOYEES，是對的。所以這個TM這個表級鎖在哪個表上，根據ID1對應的ID可以找出來；另外結果中TX所在的行，有ID1對應的ID和ID2列對應的ID，ID1和ID2這兩個數字標示著這個事務用的那個回滾段、事務表里面的槽位號還有覆蓋次數，我們可以通過一個sql語句查出來

將ID1拆解

select trunc(393249/power(2,16)) as undo_blk#,bitand(393249,to_number('ffff','xxxx')) + 0 as slot#from dual;

剛才delete語句產生的TX鎖是這一行

132 TX 262187 216 Exclusive None 0

ID1列的值262187替換上面的語句中的值393249

得到

select trunc(262187/power(2,16)) as undo_blk#,bitand(262187,to_number(‘ffff’,’xxxx’)) + 0 as slot#from dual;

執行一下

SQL> select trunc(262187/power(2,16)) as undo_blk#,bitand(262187,to_number('ffff','xxxx')) + 0 as slot# from dual; 2
UNDO_BLK# SLOT#---------- ---------- 4 43

看結果是4號回滾段，回滾段事務表中槽位號SLOT#是43

和以前查詢結果是一樣的

SQL> select xidusn,xidslot,xidsqn,status from v$transaction;
XIDUSN XIDSLOT XIDSQN STATUS---------- ---------- ---------- ---------------- 4 43 216 ACTIVE

剛才這個語句XIDSQN的值216

(#A)語句結果行

132 TX 262187 216 Exclusive None 0

直接有了

(#A)語句結果中ID2是覆蓋次數

通過這個sql語句

select trunc(262187/power(2,16)) as undo_blk#,bitand(262187,to_number(‘ffff’,’xxxx’)) + 0 as slot#from dual; 2

找出來用的哪個回滾段、槽位號是多少、覆蓋次數這三個信息

也就是(#A)語句結果中TX這一行，ID1列和ID2列可以找到哪個事務

行是TM鎖

132 TM 51857 0 Row Exclusive None 0

ID1對應的編號51857是對象的編號，這是v$lock

Oracle中的表級鎖有

鎖定模式鎖定簡稱編碼數值Row Exclusive RX 3Row Shared RS 2Share S 4Exclusive X 6Share Row Exclusive SRX 5NULL N/A 0或者1

這是鎖的一些編號，v$lock視圖里面是用編號給列出來了，如編號3對應Row Exclusive鎖模式，并且v$lock持有鎖和申請鎖的都列出來了，我們查詢以后得出的結論，SID為132的LOCK_MODE它是持有鎖的模式。

(#A)結果中SID為132的行有9行，它是持有這么多的鎖，REQUEST_MODE都是None，請求這塊是none

我們看(#A)結果中這些列，SID是session id，Type是類型，有表級鎖TM和事務鎖TX，對于ID，對TM來講ID1是哪個對象，對于TX來講ID1和ID2分別對應哪個事務。

LOCK_MODE是SID會話持有的鎖，它持有這個鎖，REQUEST_MODE這個是請求什么鎖，這里我請求都是None，沒有請求任何鎖，同時，BLOCK這個數字是0或者1，其中：這個0表示，比如說SID為132的會話持有LOCK_MODE為Row share的鎖，這個鎖并沒有把其它的別人鎖住，BLOCK就為0；BLOCK如果是1的話，132持有這個鎖同時還鎖住了別人到底鎖住了誰可以去查。

BLOCK是這個意思，這里結果中block都是0，也就是對132來講，我持有這么多的鎖，但是沒有鎖其它的任何人，也就是132持有的鎖沒有對別人造成影響。

三、v$enqueue_lock視圖

v$enqueue_lock該視圖中包含的字段以及字段含義與v$lock中的字段一模一樣。只不過該視圖中只顯示那些申請鎖定，但是無法獲得鎖定的session信息。其中的記錄按照申請鎖定的時間先后順序排列，先申請鎖定的session排在前面，排在前面的session將會先獲得鎖定。

我們接著看v$enqueue_lock，vlock是把會話持有的鎖和請求的鎖全給列出來了，v$enqueue_lock只是把請求鎖的都列出來了，v$enqueue_lock只是把哪些會話它正在請求鎖，它把請求的列出來了，它持有鎖它沒列；因為對我們來講，有時候我們只關心誰在請求鎖，因為請求鎖就有可能被鎖住，但有時候我們并不關心持有鎖

四、v$locked_object視圖

v$locked_object記錄了當前已經被鎖定的對象的信息

XIDUSN表示當前事務使用的回滾段的編號

XIDSLOT說明該事務在回滾段頭部的事務表中對應的記錄編號

XIDSQN說明序列號

OBJECT_ID說明當前被鎖定的對象的ID號，可以根據該ID號到dba_objects里查找被鎖定的對象名稱

LOCKED_MODE說明鎖定模式的數字編碼

v$locked_object記錄了當前已經被鎖定的對象的信息，哪些對象被鎖定了

XIDUSN、XIDSLOT、XIDSQN是鎖這些對象的事務信息

OBJECT_ID是哪個對象被鎖住了

LOCKED_MODE是鎖的模式是什么，用什么方式鎖了，比如某個表被鎖住的話這里面可以查出來

五、v$session視圖

v$session記錄了當前session的相關信息

SID表示session的編號

SERIAL#表示序列號

SID和SERIAL#可以認為是v$session的主鍵，它們共同唯一標識一個session

記錄的是會話信息，通過SID和SERIAL#，它倆可以唯一的標示一個會話

六、選擇hr用戶做實驗

1）hr用戶

在sqldeveloper里面開的會話有的是用hr用戶登陸的，sqldeveloper打開后，在連接設置卡部分，選擇其中的一個連接，右鍵點擊連接名，再點擊右鍵菜單中的屬性，打開新建/選擇數據庫連接設置卡，將其中的用戶名和口令改成你需要的用戶，我們使用hr做的測試，這里用hr，這個sqldeveloper就是使用hr用戶，也可以在sqldeveloper中使用sys用戶，這里使用的sqlplus里面都是用hr用戶登的。

hr用戶它默認不能訪問v$mystat，這個視圖要經常被使用，可以使用下面的命令：

grant select on v_$mystat to hr;

以管理員身份運行，給hr用戶授權，在sys用戶里面執行一下

SQL> grant select on v_$mystat to hr;
Grant succeeded.

執行以后這個hr用戶就可以訪問v$mystat了

我們看一下比如這個

select sid from v$mystat where rownum=1;

看看會話的當前的SID是多少，如果grant沒有執行的話，hr用戶使用這個語句查詢它會報錯的，它會報沒有這個對象，去grant一下以后它就ok了，所以你需要用sys用戶grant一下

在sqldeveloper中，一個hr用戶建立的連接返回了一個結果

SID 134

當前的SID是134，我們看看第二個hr用戶建立的會話

SID131

執行時返回結果慢，在sqldeveloper執行慢主要和sqldeveloper的原理有關系，當一個會話長時間不用的時候它會關掉，sqldeveloper確實非常占資源，我們現在開了三個所以非常的慢了。

在sqlplus中也可以查，這個比較的快

SQL> select sid from v$mystat where rownum=1;
SID---------- 139

這個hr用戶會話的SID是139，在另一個sqlplus中查

SQL> select sid from v$mystat where rownum=1;
SID---------- 145

這個hr用戶會話的SID是145，簡單記錄一下134,131，第三個sqldeveloper不用，第四個是sqlplus是139，第五個sqlplus是145，hr用戶一共建了了四個會話，SID分別是134,131,139,145

最終放棄了使用sqldeveloper做hr用戶的實驗，后面的實驗只使用了前面hr會話中的139和145的兩個會話

2）v_$mystat和v$mystat

可能大家注意到了，grant語句中使用的v_$mystat和hr用戶訪問的v$mystat不一樣，這里補充說一下 v$mystat 和 v_$mystat 的區別。

初始狀態下hr用戶訪問v$mystat時

SQL> select sid from v$mystat where rownum=1;select sid from v$mystat where rownum=1 *ERROR at line 1:ORA-00942: table or view does not exist

提示訪問的表或者視圖不存在，當sys用戶給hr用戶授權后

grant select on v_$mystat to hr;

hr用戶就可以訪問了，大家注意到上面兩個語句中執行的對象并不一樣，要訪問的是v$mystat，而授權的是v_$mystat，如果我們直接給v$mystat授權

SQL> grant select on v$mystat to hr;grant select on v$mystat to hr *ERROR at line 1:ORA-02030: can only select from fixed tables/views

說明這里授權的v$mystat不是固有視圖，不能直接授權，實際這里hr訪問的v$mystat是一個同義詞，sys不能給同義詞授權，只能授權給固定的表或視圖，同義詞在Oracle中可以理解為一個對象的別名，有私有和共用之分，每個用戶都可以給自己的對象創建自己的同義詞，這樣創建的同義詞只能自己使用。

創建私有同義詞語法：

Create [OR REPLACE] SYNONYM [schema.]synonym_name FOR [schema.]object_name;

默認只有系統管理員可以創建共用同義詞，共用同義詞屬于Oracle的public，public擁有了的權限，Oracle所有的用戶都自動擁有了并可以使用

創建公有同義詞語法：

Create PUBLIC SYNONYM synonym_name FOR [schema.]object_name;

刪除同義詞的語法：

drop [public] synonym 同義詞名稱;

私有同義詞不能和自己已有的對象同名，公用同義詞可以和創建者已有的對象同名，當一個用戶的一個對象和公有同義詞同名時，使用時自己的對象優先；私有同義詞和共用同義詞可以同名，使用時私有同義詞優先。

所以這幾種對象如果有同名，語句中的使用順序是：先使用自己的固有對象或私有同義詞，最后使用公用同義詞。

查詢V$MYSTAT的說明：

SQL> select * from dict where table_name='V$MYSTAT';
TABLE_NAME------------------------------COMMENTS------------------------------ V$MYSTATSynonym for V_$MYSTAT

V$MYSTAT是一個同義詞是V_$MYSTAT的同義詞，再在庫中的同義詞數據字典中查找這個同義詞

SQL> select * from dba_synonyms where SYNONYM_NAME='V$MYSTAT';
OWNER SYNONYM_NAME TABLE_OWNE TABLE_NAME DB_LINK---------- -------------------- ---------- -------------------- --------------------PUBLIC V$MYSTAT SYS V_$MYSTAT

說明V$MYSTAT是SYS用戶的V_$MYSTAT視圖的共用同義詞，查詢V_$MYSTAT視圖的定義

SQL> select OWNER,VIEW_NAME,TEXT from dba_views where view_name = 'V_$MYSTAT';
OWNER VIEW_NAME TEXT---------- ------------------------------ --------------------------------------------------------------------------------SYS V_$MYSTAT select "SID","STATISTIC#","VALUE" from v$mystat

TEXT字段是這個視圖的定義

select “SID”,”STATISTIC#”,”VALUE” from v$mystat

也可以通過dbms_metadata.get_ddl查詢V_$MYSTAT視圖的定義，dbms_metadata.get_ddl返回的是long類型的結果，long型數據為可變長字符串，最大長度限制是2GB，sqlplus處理long型數據非常困難，因為里面存的數據一般都很長，sqlplus顯示時一般只能顯示出來一部分，所以想顯示完整的long型數據，要先給sqlplus環境設置LONG參數

SQL> SET LONG 9999SQL> select dbms_metadata.get_ddl('VIEW','V_$MYSTAT') from dual;
DBMS_METADATA.GET_DDL('VIEW','V_$MYSTAT')--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."V_$MYSTAT" ("SID", "STATISTIC#", "VALUE") AS select "SID","STATISTIC#","VALUE" from v$mystat

結果和從dba_views得到的一樣，這里又有一個v$mystat，前面的一個是同義詞，這一個看看視圖定義里面有沒有，而視圖的定義在v$fixed_view_definition中有

這里查詢V$MYSTAT的定義

SQL> select * from v$fixed_view_definition where VIEW_NAME = 'V$MYSTAT';
VIEW_NAME------------------------------VIEW_DEFINITION--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------V$MYSTATselect SID , STATISTIC# , VALUE from GV$MYSTAT where inst_id = USERENV('Instance')

說明有一個固定視圖也叫V$MYSTAT它來源于GV$MYSTAT，經過查找和分析，GV$MYSTAT同樣有一個同名的PUBLIC同義詞和一個固定視圖，而且也有一個關聯的GV_$MYSTAT視圖PUBLIC同義詞，V$MYSTAT來自于GV_$MYSTAT，而GV_$MYSTAT又產生自固定視圖GV$MYSTAT

再看一下固定視圖GV$MYSTAT的定義

SQL> select * from v$fixed_view_definition where VIEW_NAME = 'GV$MYSTAT';
VIEW_NAME------------------------------VIEW_DEFINITION--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------GV$MYSTATselect inst_id,ksusenum,ksusestn,ksusestv from x$ksumysta where bitand(ksspaflg,1)!=0 and bitand(ksuseflg,1)!=0 and ksusestn<(select ksusgstl from x$ksusgif)

說明固定視圖GV$MYSTAT來自于x$ksumysta，這里x$ksumysta表中的信息是Oracle實例當前會話的狀態信息，可以從v$fixed_table繼續查到x$ksumysta和結果中出現的x$ksusgif的信息

SQL> SELECT * FROM v$fixed_table WHERE NAME in ('X$KSUMYSTA','X$KSUSGIF');
NAME OBJECT_ID TYPE TABLE_NUM------------------------------ ---------- ----- ----------X$KSUSGIF 4294951930 TABLE 33X$KSUMYSTA 4294951106 TABLE 35

這是固定表，不用再繼續找了，一般用戶使用的V$MYSTAT是一個共用同義詞，它的來源渠道追溯過程是：V$MYSTAT共用同義詞，來自于V_$MYSTAT視圖、V$MYSTAT固定視圖、GV$MYSTAT、固定表X$KSUMYSTA，所以V$MYSTAT最終來自于固定表X$KSUMYSTA。

前面查詢中用到的dict實際也是個共用同義詞，SYS用戶的DICTIONARY視圖有兩個共用同義詞DICT和DICTIONARY，我們使用了DICT同義詞，DICTIONARY是一個數據字典，官方描述是：DICTIONARY contains descriptions of data dictionary tables and views. 就是DICTIONARY是個數據字典，內容中包含Oracle系統中所有數據字典，包括所有數據字典表和數據字典視圖的名稱和說明。

3）Oracle中的PUBLIC角色

前面講了一個共用同義詞創建時，自動的屬于了PUBLIC，PUBLIC在Oracle中比較特殊，有很多人弄不清楚它到底是什么類型的對象。

查詢PUBLIC在角色中有沒有定義：

SQL> select dbms_metadata.get_ddl('ROLE','PUBLIC') from dual;
DBMS_METADATA.GET_DDL('ROLE','PUBLIC')--------------------------------------------------------------------------------
CREATE ROLE "PUBLIC"

查詢PUBLIC在用戶中有沒有定義：

SQL> select dbms_metadata.get_ddl('USER','PUBLIC') from dual;ERROR:ORA-31603: object "PUBLIC" of type USER not found in schema "SYS"ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105ORA-06512: at "SYS.DBMS_METADATA", line 2805ORA-06512: at "SYS.DBMS_METADATA", line 4333ORA-06512: at line 1
no rows selected

所以PUBLIC是一個角色，不是用戶，但是在DBA_ROLES查詢

SQL> select * from DBA_ROLES;
ROLE PASSWORD------------------------------ --------CONNECT NORESOURCE NODBA NOSELECT_CATALOG_ROLE NOEXECUTE_CATALOG_ROLE NODELETE_CATALOG_ROLE NOEXP_FULL_DATABASE NOIMP_FULL_DATABASE NORECOVERY_CATALOG_OWNER NOGATHER_SYSTEM_STATISTICS NOLOGSTDBY_ADMINISTRATOR NOAQ_ADMINISTRATOR_ROLE NOAQ_USER_ROLE NOGLOBAL_AQ_USER_ROLE GLOBALSCHEDULER_ADMIN NOHS_ADMIN_ROLE NOAUTHENTICATEDUSER NOOEM_ADVISOR NOOEM_MONITOR NOWM_ADMIN_ROLE NOJAVAUSERPRIV NOJAVAIDPRIV NOJAVASYSPRIV NOJAVADEBUGPRIV NOEJBCLIENT NOJAVA_ADMIN NOJAVA_DEPLOY NOCTXAPP NOXDBADMIN NOXDBWEBSERVICES NOOLAP_DBA NOOLAP_USER NOMGMT_USER NO
33 rows selected.

并沒有PUBLIC。

查詢DBA_ROLES的定義

SQL> select dbms_metadata.get_ddl('VIEW','DBA_ROLES','SYS') from dual;
DBMS_METADATA.GET_DDL('VIEW','DBA_ROLES','SYS')--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."DBA_ROLES" ("ROLE", "PASSWORD_REQUIRED") AS select name, decode(password, null, 'NO', 'EXTERNAL', 'EXTERNAL', 'GLOBAL', 'GLOBAL', 'YES')from user$where type# = 0 and name not in ('PUBLIC', '_NEXT_USER')

結果中有這么一句，name not in (‘PUBLIC’, ‘_NEXT_USER’)，說明DBA_ROLES生成時過濾掉了PUBLIC

Oracle中的角色是一種權限的集合，如常用的CONNECT連接角色，RESOURCE資源角色，DBA數據庫管理員角色是ORACLE系統的三個內置角色，可以把單個的權限再賦予角色使角色的權限增加

如：

SQL> select ROLE, PRIVILEGE from role_sys_privs where role='CONNECT';ROLE PRIVILEGE------------------------------ ----------------------------------------CONNECT CREATE SESSIONSQL> grant CREATE ANY VIEW to CONNECT;Grant succeeded.SQL> select ROLE, PRIVILEGE from role_sys_privs where role='CONNECT';ROLE PRIVILEGE------------------------------ ----------------------------------------CONNECT CREATE ANY VIEWCONNECT CREATE SESSION

也可以回收角色的權限

SQL> revoke CREATE ANY VIEW from CONNECT;
Revoke succeeded.
SQL> select ROLE, PRIVILEGE from role_sys_privs where role='CONNECT';
ROLE PRIVILEGE------------------------------ ----------------------------------------CONNECT CREATE SESSION

但最終這些角色和單個的權限是要被賦予用戶來起作用的

查看用戶擁有的權限

select grantee,privilege from dba_sys_privs where grantee=’HR’;

如結果：

SQL> select grantee,privilege from dba_sys_privs where grantee='HR';
GRANTEE PRIVILEGE------------------------------ ----------------------------------------HR CREATE VIEWHR UNLIMITED TABLESPACEHR CREATE DATABASE LINKHR CREATE SEQUENCEHR CREATE SESSIONHR ALTER SESSIONHR CREATE SYNONYM
7 rows selected.

使用授權語句時可以把單個權限分別賦予單個用戶，也可以把權限的集合角色授予一個用戶，我們可以把權限賦予PUBLIC

SQL> grant CREATE ANY VIEW to PUBLIC;Grant succeeded.

同時也可以把PUBLIC賦予用戶

SQL> grant PUBLIC to HR;Grant succeeded.

如果把用戶賦予用戶是不允許的：

SQL> grant sys to HR;grant sys to HR *ERROR at line 1:ORA-01919: role 'SYS' does not exist

進一步說明PUBLIC是個角色，再查HR用戶的權限，HR的系統權限

SQL> select * from dba_sys_privs where grantee = 'HR';GRANTEE PRIVILEGE ADM------------------------------ ---------------------------------------- ---HR CREATE VIEW NOHR UNLIMITED TABLESPACE NOHR CREATE DATABASE LINK NOHR CREATE SEQUENCE NOHR CREATE SESSION NOHR ALTER SESSION NOHR CREATE SYNONYM NO7 rows selected.

HR的角色權限

SQL> select * from dba_role_privs where grantee = 'HR';GRANTEE GRANTED_ROLE ADM DEF------------------------------ ------------------------------ --- ---HR RESOURCE NO YESHR PUBLIC NO YES

這時HR用戶我們看到在已經賦予的角色GRANTED_ROLE中有了PUBLIC角色權限，這是我前面手動賦予了HR用戶的權限，盡管它原來就有，但在這里顯示出來了，而HR用戶初始的角色權限是這樣的：

SQL> select * from dba_role_privs where grantee = 'HR';GRANTEE GRANTED_ROLE ADM DEF------------------------------ ------------------------------ --- ---HR RESOURCE NO YES

這是HR角色權限的默認狀態，所以可以確定PUBLIC是一個角色了，但這個角色比較特殊，在很多的表和視圖都給屏蔽掉了，但有的地方還是可以查到的

SQL> select * from dba_role_privs where grantee = 'PUBLIC';
GRANTEE GRANTED_ROLE ADM DEF------------------------------ ------------------------------ --- ---PUBLIC RESOURCE NO YES
SQL> select * from dba_sys_privs where grantee = 'PUBLIC';
GRANTEE PRIVILEGE ADM------------------------------ ---------------------------------------- ---PUBLIC UNLIMITED TABLESPACE NOPUBLIC CREATE ANY VIEW NO

這兩個查詢列出了PUBLIC擁有的角色權限和系統權限，同樣也可以給PUBLIC添加新的權限，CREATE ANY VIEW就是我自己給它添加的，public擁有的權限，所有的用戶都自動的擁有了，也就是所有的用戶初始默認都擁有PUBLIC角色的權限。

七、兩個事務間鎖爭用實例

1）兩個事務爭用鎖

我們執行一個update employees set last_name=last_name||’a’ where department_id=60;開始一個事務，開始實驗，在其中一個sqlplus中執行

SQL> update employees set last_name=last_name||'a' where department_id=60;5 rows updated.

之前提過，這個事務一旦開始以后伴隨著一堆的鎖，執行這個語句的SID是139

我們先查一下和事務相關的

select xidusn,xidslot,xidsqn,status from v$transaction;

剛才已經開始一個事務了，使用sys用戶看一下有多少事務，因為剛開始一個事務，它是active的沒有提交，它在v$transaction里面一定會出現

SQL> select xidusn,xidslot,xidsqn,status from v$transaction; XIDUSN XIDSLOT XIDSQN STATUS---------- ---------- ---------- ---------------- 7 23 238 ACTIVE

這就是剛才我們的事務，然后我們可以去查一下，剛才在139開始的一個事務，sys使用下面的語句查一下

查詢結果

從結果看139會話，產生了一個TM鎖和一個TX鎖。

TM的ID2總是0，ID1是代表著操作所在的表，TX鎖的ID1和ID2通過語句可以找到這個事務，從LOCK_MODE看出他們都持有鎖，REQUEST_MODE看出都沒有請求鎖，從BLOCK都是0看出持有的鎖都沒有阻塞別人，再另外開一個session

同樣的去執行

update employees set last_name=last_name||’b’ where department_id=60;

在SID為145的hr會話中執行，它需要的資源被鎖住

SQL> update employees set last_name=last_name||'b' where department_id=60;

這時的執行被卡住

我們再去查一下這里是139和145，對(#A)語句稍作修改

得到語句

select sid,type,id1,id2,decode(lmode,0,’None’,1,’Null’,2,’Row share’,3,’Row Exclusive’,4,’Share’,5,’Share Row Exclusive’,6,’Exclusive’) lock_mode,decode(request,0,’None’,1,’Null’,2,’Row share’,3,’Row Exclusive’,4,’Share’,5,’Share Row Exclusive’,6,’Exclusive’) request_mode,blockfrom v$lockwhere sid in(139,145)order by sid;

查的是v$lock，看看這個鎖的狀況，好執行一下，結果

SQL> select sid,type,id1,id2,decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode, 2 3 decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,blockfrom v$lock 4 5 where sid in(139,145) 6 order by sid; SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK---------- -- ---------- ---------- ------------------- ------------------- ---------- 139 TM 51857 0 Row Exclusive None 0 139 TX 458775 238 Exclusive None 1 145 TM 51857 0 Row Exclusive None 0 145 TX 458775 238 None Exclusive 0

139和145都出現了，139的TM和TX鎖沒變，一開始執行的139，后面執行的145，139一開始執行的，持有TX和TM鎖，145的TM鎖LOCK_MODE為Row Exclusive，持有145和139的TM鎖的ID1相同就是對象還一樣，也就是說139和145都在這個51857對象上加了RX鎖；但是145的TX鎖行的REQUEST_MODE的值是Exclusive出現了Exclusive，也就是145被139這個事務鎖住了。

然后我們看139的TX這行，BLOCK的值是1說明阻塞了別人，阻塞了145，而145 TX的REQUEST_MODE是Exclusive，它正在請求Exclusive鎖，也就是被鎖住了

通過這個我們看到一些問題，但是我們知道這個鎖出現這個問

題，也不見得有問題，因為鎖住很正常，139一旦提交以后，145馬上就獲取到這個鎖了

2）關于等待鎖中的ID1和ID2

另外從結果我們可能發現一個問題

139 TX 458775 238 Exclusive None 1 145 TX 458775 238 None Exclusive 0

我們看到139會話和145會話的TX鎖的ID1和ID2是相同的，這里的145的鎖狀態的ID1和ID2并不是145會話的事務信息，145會話的TX鎖的REQUEST_MODE為Exclusive說明它在請求一個鎖，這個例子中145自己本身的事務還沒有開始，這時查詢v$transaction并沒有145會話的事務。

TX鎖REQUEST_MODE為Exclusive時，這里的ID1和ID2的值是被請求鎖的事務信息，這里在請求139會話的鎖，這里ID1和ID2的值就是139會話的信息

當145得到鎖以后，本例中這時145會話開始了一個事務，這里的ID1和ID2 的值會自動改變為145事務的信息，REQUEST_MODE為Exclusive的鎖ID1和ID2的信息始終是被請求的持有鎖的事務的信息，有多個事務等待同一個鎖，前一個持有鎖的事務釋放鎖后，一個新事務得到了這個鎖，這時隊列中的其它事務的Exclusive狀態的鎖信息的ID1和ID2都變為了這個新持有鎖的事務的信息。

如果145事務在請求鎖之前，145已經開始了一個事務，也就是它已經持有了事務鎖，這時的結果會把它本身的事務鎖也列出，并且ID1和ID2的值是145事務的信息，同時也會列出它正在請求的鎖的信息，這條信息的ID1和ID2是被請求鎖的信息

做出了下面的一個例子的結果

SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK---------- -- ---------- ---------- ------------------- ------------------- ---------- 148 TX 262165 242 None Exclusive 0 148 TM 51857 0 Row Exclusive None 0 148 TX 524327 303 Exclusive None 0 150 TX 262165 242 Exclusive None 1 150 TM 51857 0 Row Exclusive None 0

148會話本身持有一個TX鎖

148 TX 524327 303 Exclusive None 0

這條信息的ID1和ID2的信息是它本身事務的信息，148會話還請求一個TX鎖

148 TX 262165 242 None Exclusive 0

這條信息的ID1和ID2的信息是148正在請求的被請求鎖的事務的信息，這里正在請求150正在持有的鎖，所以這行的ID1和ID2列出了150事務的信息

有一行

150 TX 262165 242 Exclusive None 1

這行的BLOCK為1，說明150事務它持有的鎖有別的事務正在等待，正好和148正在請求鎖對應，驗證了前面的分析

八、三個事務的鎖爭用

1）三個事務爭同一個鎖

我們再打開一個會話，再新建一個會話，以hr用戶身份連接

先查一下SID

SQL> select sid from v$mystat where rownum=1; SID---------- 136

這個新的當前會話的SID是136，接著前面的實驗，也去做同樣的一個操作

update employees set last_name=last_name||’b’ where department_id=60;

它肯定也被鎖住

執行結果

SQL> update employees set last_name=last_name||'b' where department_id=60;

暫時無返回值，也被鎖住，這里是136，現在是139 145 和136操作，139應該把145和136鎖住了

再去查一個語句

select sid,type, decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode from v$enqueue_lock where sid in(145,136);

根據分析兩個被鎖住的是145和136，查的是v$enqueue_lock，這里面它會只是把誰被鎖住了誰給列出來，請求鎖的被列出來，執行一下，這里訪問的是v$enqueue_lock

執行結果

SQL> select sid,type, decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode from v$enqueue_lock where sid in(145,136); 2 3 4 5 SID TY REQUEST_MODE---------- -- ------------------- 145 TX Exclusive 136 TX Exclusive

我們看145和136都在，v$enqueue_lock列出的都在請求鎖，145和136都在請求事務鎖，都被別人鎖住了，其實139鎖住了145 和 136，根據語句執行的順序，145是第一個被鎖住的，136是第二個被鎖住的

這里面如果我139釋放了的話，139把145和136同時鎖住了，第一個獲得鎖的應該是145，再就是136，鎖是可以排隊的。

我們看一個TX鎖這個鎖是139的，這個鎖把145和136同時鎖住了，145和136會到139下面去排隊，先是145，后面是136要過來排隊，139釋放以后，145第一個獲取，第一個獲得鎖，有可能獲得鎖以后145又把136鎖住了。

如果它們獲取一樣的資源，145把136鎖住了，如果說139釋放以后，145獲取的資源和136獲取的資源不一樣的話，這兩個可以同時獲取到鎖。通過這個我們可以看出并記住鎖是排隊的。

2）v$lock中BLOCK字段的值

舉一個三個事務爭用相同鎖的例子，查詢v$lock視圖的結果

SQL> select sid,type,id1,id2,decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode, 2 3 decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,block 4 from v$lock 5 where sid in(132,135,139) 6 order by sid;
SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK---------- -- ---------- ---------- ------------------- ------------------- ---------- 132 TM 51857 0 Row Exclusive None 0 132 TX 655370 242 Exclusive None 1 135 TM 51857 0 Row Exclusive None 0 135 TX 655370 242 None Exclusive 0 139 TM 51857 0 Row Exclusive None 0 139 TX 655370 242 None Exclusive 06 rows selected.

這三個事務開始執行的順序是132,135,139，這時132事務的信息是ID1：655370 ，ID2：242，135和139會話中的事務還沒有開始，執行rollback釋放第一個事務占用的鎖，然后執行相同的語句

得到的結果是：

SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK---------- -- ---------- ---------- ------------------- ------------------- ---------- 135 TM 51857 0 Row Exclusive None 0 135 TX 327683 338 Exclusive None 1 139 TM 51857 0 Row Exclusive None 0 139 TX 327683 338 None Exclusive 0

132事務釋放鎖后，第一個排隊的135得到了鎖，得到鎖后135會話中的事務就開始了，這時135會話中事務的信息是ID1：327683，ID2：338，這時它鎖住了139，139還在等待，39會話中的事務仍然沒有開始

不管是1個事務還是2個事務在等待鎖，持有鎖的事務的信息的BLOCK都為1，這個字段并不是說明有多少個事務在等待鎖，只是說明有沒有事務在等待這個鎖

等待鎖的139會話

在第一次查詢時的結果

139 TX 655370 242 None Exclusive 0

在第二次查詢時的結果

139 TX 327683 338 None Exclusive 0

ID1和ID2的值變化了，但都是139它等待的當前正在持有這個鎖的事務的信息

九、鎖的時間

我們找一個非常有意義的一個

select a.sid blocker_sid,a.serial#,a.username as blocker_username,b.type,decode(b.lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,b.ctime as time_held,c.sid as waiter_sid,decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,c.ctime time_waited from v$lock b, v$enqueue_lock c, v$session a where a.sid = b.sid and b.id1= c.id1(+) and b.id2 = c.id2(+) and c.type(+) = 'TX' and b.type = 'TX' and b.block = 1order by time_held, time_waited;

這個sql語句是我們用的最多的一個sql語句，它做一件什么事情呢？

SQL> select a.sid blocker_sid,a.serial#,a.username as blocker_username,b.type,decode(b.lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode, 2 3 b.ctime as time_held,c.sid as waiter_sid, 4 decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode, 5 c.ctime time_waited 6 from v$lock b, v$enqueue_lock c, v$session awhere a.sid = b.sid and b.id1= c.id1(+) and b.id2 = c.id2(+) and c.type(+) = 'TX' and b.type = 'TX' and b.block = 1 7 8 order by time_held, time_waited;BLOCKER_SID SERIAL# BLOCKER_USERNAME TY LOCK_MODE TIME_HELD WAITER_SID REQUEST_MODE TIME_WAITED----------- ---------- ------------------------------ -- ------------------- ---------- ---------- ------------------- ----------- 139 2746 HR TX Exclusive 3909 136 Exclusive 1790 139 2746 HR TX Exclusive 3909 145 Exclusive 2931

前兩個字段，BLOCKER_SID為139和SERIAL#為2746標明一個會話，這個會話使用BLOCKER_USERNAME為hr用戶登陸的，它的TY是TX鎖，它持有TIME_HELD為3909厘秒。

第一行WAITER_SID為136事務，TIME_WAITED等待了1790這么長時間，也就是說136目前在等待139，就是136被139鎖住了，139持有鎖的時間是TIME_HELD 3909這么長了，WAITER_SID 136等待TIME_WAITED 1790這么長了，有了時間了就能判斷這個鎖是不是有沒有問題。

還有一個136等待了1790這么長的時間，145等待139等待了2931這么長時間，就說明145比136等的時間長了，過了一段時間再執行一次上面的命令

SQL> select a.sid blocker_sid,a.serial#,a.username as blocker_username,b.type,decode(b.lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode, 2 3 b.ctime as time_held,c.sid as waiter_sid, 4 decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode, 5 c.ctime time_waitedfrom v$lock b, v$enqueue_lock c, v$session a 6 7 where a.sid = b.sid and b.id1= c.id1(+) and b.id2 = c.id2(+) and c.type(+) = 'TX' and b.type = 'TX' and b.block = 1 8 order by time_held, time_waited;BLOCKER_SID SERIAL# BLOCKER_USERNAME TY LOCK_MODE TIME_HELD WAITER_SID REQUEST_MODE TIME_WAITED----------- ---------- ------------------------------ -- ------------------- ---------- ---------- ------------------- ----------- 139 2746 HR TX Exclusive 6334 136 Exclusive 4215 139 2746 HR TX Exclusive 6334 145 Exclusive 5356

145等待的時間比136時間長，也就是從某種意義上來講145它排在136的前面，這個命令是有意義的

我們就看TIME_WAITED列，再看TIME_HELD，如果你持有時間太長了，也就是說明你這個事務遲遲不提交，就根據BLOCKER_SID和SERIAL#這里是139和2746，就可以執行一個sql語句

可以用

alter system kill session ‘139,2746’;

把它kill掉，執行這個命令，執行它以后，它就可以把139給kill掉，kill以后它就會自動回滾

系統管理員sys會話中做一下

SQL> alter system kill session '139,2746';System altered.

kill以后我們看，139已經kill掉了，這時我們看145

SQL> update employees set last_name=last_name||'b' where department_id=60;5 rows updated.

145的等待狀態解除了，update操作成功了，也就是145現在持有了鎖，136仍在等待，136還被鎖著得不到執行，因為145又把136鎖了

我們再去查

SQL> select a.sid blocker_sid,a.serial#,a.username as blocker_username,b.type,decode(b.lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,b.ctime as time_held,c.sid as waiter_sid, 2 3 4 decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode, 5 c.ctime time_waited 6 from v$lock b, v$enqueue_lock c, v$session awhere a.sid = b.sid and b.id1= c.id1(+) and b.id2 = c.id2(+) and c.type(+) = 'TX' and b.type = 'TX' and b.block = 1 7 8 order by time_held, time_waited;BLOCKER_SID SERIAL# BLOCKER_USERNAME TY LOCK_MODE TIME_HELD WAITER_SID REQUEST_MODE TIME_WAITED----------- ---------- ------------------------------ -- ------------------- ---------- ---------- ------------------- ----------- 145 1015 HR TX Exclusive 221 136 Exclusive 221

現在是145持有鎖，但它阻塞了WAITER_SID 為136的會話，139會話就沒鎖了

我們把剛才的事務都回滾了，在139會話中執行

SQL> rollback;rollback*ERROR at line 1:ORA-00028: your session has been killed

看出session 139已經被kill了，再把145的會話回滾了

SQL> rollback;Rollback complete.

再把136的會話回滾了

SQL> update employees set last_name=last_name||'b' where department_id=60;5 rows updated.SQL> rollback;Rollback complete.

136會話在145會話回滾后得到了執行，最終它也得到了鎖

為了試驗把它也回滾了

十、一個事務多個TM鎖

一個事務修改多行，產生一個TX鎖，可以在多個表上產生多個TM鎖，一個事務只產生一個事務鎖TX鎖

我們在一個事務里面多執行幾條sql語句

update employees set last_name=last_name||'a' where department_id=60;update departments set department_name='unknow' where department_id=10;update locations set city='unknown' where location_id=1100;

在一個hr會話

SQL> select sid from v$mystat where rownum=1; SID--------- 132

先執行一條update

SQL> update employees set last_name=last_name||'a' where department_id=60;5 rows updated.

更新了employees這個表

第二個語句，它接著更新departments

SQL> update departments set department_name='unknow' where department_id=10;1 row updated.

都是一個事務里面的，下面語句是更新locations

SQL> update locations set city='unknown' where location_id=1100;1 row updated.

更新了三個語句，然后我們再使用

select sid,type,id1,id2,decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')request_mode,blockfrom v$lockwhere sid=132;

再去查132這個會話它持有鎖的情況，在sys用戶會話中執行結果

SQL> select sid,type,id1,id2,decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode, 2 3 decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')request_mode,block 4 from v$lock 5 where sid=132;
SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK---------- -- ---------- ---------- ------------------- ------------------- ---------- 132 TM 51857 0 Row Exclusive None 0 132 TM 51852 0 Row Exclusive None 0 132 TM 51847 0 Row Exclusive None 0 132 TX 589860 329 Exclusive None 0

一個會話的一個事務它修改了三個表，對三個表產生TM鎖，它產生了一個TX鎖，TX鎖就只有一個。

十一、transactions和dml_locks參數

再看一個sql語句

select name,value from v$parameter where name in(‘transactions’,’dml_locks’);

可以獲得的TX鎖定的總個數由初始化參數transactions決定，而可以獲得的TM鎖定的個數則由初始化參數dml_locks決定

transactions參數表示Oracle一個實例最多可有的事務數

dml_locks參數表示一個Oracle實例中最多可產生的TM鎖就是表級鎖的數量

對整個數據庫來講

它能獲得的TX鎖和TM鎖的總數由’transactions’和’dml_locks’它倆限制

如果這兩個參數過小的話

有可能影響并發的事務的數量以及訪問的表的數量

我們執行一下看有多大

SQL> select name,value from v$parameter where name in('transactions','dml_locks');NAME VALUE--------------- ----------dml_locks 748transactions 187

這個有時候也會碰到一些問題，結果中一個dml_locks是748，一個transactions是187，對數據庫來講，同時可以有187個事務可以同時運行，而鎖的數量，同時修改的表可以有700多個。

一般的我們把這個都修改的抬高一些，比如把transactions修改為300，比如dml_locks我們修改成1500，可以給它增加，到底該不該增加，我們有一個查詢

select resource_name as "R_N",current_utilization as "C_U",max_utilization as "M_U",initial_allocation as "I_U" from v$resource_limit where resource_name in('transactions','dml_locks');

這個很有用，很有幫助，有v$resource_limit這個視圖，我們大家可以查一下，里面有好多的信息

我們先查這一個transactions和dml_locks參數

SQL> select resource_name as "R_N",current_utilization as "C_U",max_utilization as "M_U",initial_allocation as "I_U"from v$resource_limitwhere resource_name in('transactions','dml_locks'); 2 3R_N C_U M_U I_U------------------------------ ---------- ---------- --------------------dml_locks 3 48 748transactions 2 9 187

R_N這個列是資源名字，如dml_locks是資源名，C_U是current_utilization當前已經使用的數目，當前鎖定了3個表，M_U是max_utilization最大同時使用的數目，最大鎖過48個，I_U是initial_allocation初始可分配的數量，最大可分配的748

這是dml_locks，當前C_U是3個，最大可以是I_U是748，M_U為48是曾經達到的最大值是48，只要這個48沒達到748，說明我這個dml_locks沒出現過問題

那么transactions，曾經最大是9個，最大可以是187，這都沒問題，v$resource_limit視圖我們查一下訪問一下，里面有很多資源

SQL> select resource_name as "R_N",current_utilization as "C_U",max_utilization as "M_U",initial_allocation as "I_U"from v$resource_limit; 2R_N C_U M_U I_U------------------------------ ---------- ---------- --------------------processes 27 37 150sessions 31 42 170enqueue_locks 13 22 2300enqueue_resources 17 38 968ges_procs 0 0 0ges_ress 0 0 0ges_locks 0 0 0ges_cache_ress 0 0 0ges_reg_msgs 0 0 0ges_big_msgs 0 0 0ges_rsv_msgs 0 0 0gcs_resources 0 0 0gcs_shadows 0 0 0dml_locks 3 48 748temporary_table_locks 0 0 UNLIMITEDtransactions 2 9 187branches 0 0 187cmtcallbk 0 2 187sort_segment_locks 0 1 UNLIMITEDmax_rollback_segments 11 11 187max_shared_servers 1 1 UNLIMITEDparallel_max_servers 0 2 4022 rows selected.

只要這個M_U的值沒有跟I_U的值相等，當然不可能超過，沒有跟它相等就說明我設置的參數都沒有問題，比如有人經常說這個processes、這個sessions是不是設小了，連不上可以看看sessions的M_U的值有沒有超過I_U的值，有沒有等于它，等于它說明可能就有問題

記住v$resource_limit這個很有意義，這里講了和事務相關的一些操作

十二、死鎖

1）死鎖的發生

Oracle里面還有一種鎖叫死鎖，Oracle一直這么說，Oracle中只要產生死鎖一定是你的應用寫的有問題，碰到死鎖的時候Oracle自動會釋放，會殺掉一個事務，一個死鎖會產生一個trc文件，我們來看什么叫死鎖一個事務要修改一個資源，A事務修改了這個資源，B事務修改了另一個資源，A事務修改了一個資源以后在這個資源上加了鎖了，事務修改了另一個資源后也加了鎖。

A想持有B正在修改的這個資源，但已被B鎖住了A修改了一個資源但是它還想修改B正在修改的資源但已被B鎖住A被B鎖住了B修改了一個資源后又想去修改A正在修改的資源B被A鎖住了

產生死鎖，并且這個結它解不開，因為，這時只有A回滾了以后，B才能持有A現在擁有的資源

死鎖以后會有什么現象呢？

我把原來實驗的會話都給它rollback，并在新會話中實驗，在session1里面，我更新100

SQL> select sid from v$mystat where rownum=1; SID---------- 150 SQL> update employees set last_name=last_name||'a'
where employee_id=100; 21 row updated.

我把100給鎖住了

然后在 session2里面呢？

SQL> select sid from v$mystat where rownum=1; SID---------- 148 SQL> update employees set last_name=last_name||'b'where employee_id=101; 21 row updated.

把101給鎖住了，A里面把100鎖住了，B把101鎖住了，然后session1想去：

SQL> update employees set last_name=last_name||'c' where employee_id=101;

想去鎖B鎖住的資源，已被B鎖住了，這時A被B鎖住了，然后session2中：

SQL> update employees set last_name=last_name||'d' where employee_id=100;

也在等待鎖，B又被A鎖住了，形成一個死循環了，這時在A里面出現

SQL> update employees set last_name=last_name||'c' where employee_id=101;update employees set last_name=last_name||'c' where employee_id=101 *ERROR at line 1:ORA-00060: deadlock detected while waiting for resource

這時候A會話馬上出一個問題，另外一個會話B中我們回車以后，這個A會話一下子檢測到死鎖，被回滾了，馬上被回滾了，這里回滾的不是A會話中的整個事務，只是被回滾了一條語句，就是把造成死鎖的那條語句給回滾了，這個事務中前面其它語句沒有影響，并沒有回滾整個的事務

如果這時查詢A會話，查詢后可以得知這條語句前執行的語句仍然有效，也就是當死鎖發生的時候，Oracle馬上會檢測，同時將其中一個事務的一條造成死鎖的語句給自動回滾

這里是回滾了第一個會話造成死鎖的語句，就是請求第二個會話占有的鎖但是未得到鎖的語句，但這時第二個會話請求的鎖，第一個會話仍然占有，既然如此，死循環被解除了，這樣在在造成死鎖的兩個會話中解除了死鎖。

我們執行rollback將其中一個會話全部回滾

SQL> rollback;Rollback complete.

并且這里又把第一個會話中占用鎖的語句回滾后

第二個會話中等待鎖的語句得到了執行

SQL> update employees set last_name=last_name||'d' where employee_id=100;1 row updated.

把第二個會話也回滾

SQL> rollback;Rollback complete.

當死循環發生的時候會做幾件事情，第一個Oracle自動的對死鎖自動的檢測，而且還能快速檢測，而且把其中一個事務給回滾，但只是回滾部分sql語句

2）死鎖的信息

同時當死鎖發生的時候會出現一件很重要的事情，Oracle會記錄下死鎖的信息

死鎖發生的時候

[oracle@redhat4 bdump]$ pwd/u01/app/oracle/admin/jiagulun/bdump

在這個目錄里面

[oracle@redhat4 bdump]$ lsalert_jiagulun.log jiagulun_lgwr_13577.trc jiagulun_mmnl_6638.trcjiagulun_cjq0_13651.trc jiagulun_lgwr_13643.trc jiagulun_p000_6646.trcjiagulun_lgwr_13460.trc jiagulun_lgwr_6626.trc jiagulun_p001_6648.trc

有alert日志alert_jiagulun.log，alert日志是數據庫的總日志，可以查看這個alert日志cat alert_jiagulun.log

執行結果

[oracle@redhat4 bdump]$ cat alert_jiagulun.logMon Apr 11 13:38:53 2016Starting ORACLE instance (normal)LICENSE_MAX_SESSION = 0LICENSE_SESSIONS_WARNING = 0Shared memory segment for instance monitoring createdTue Nov 21 06:45:15 2017MMNL absent for 63369 secs; Foregrounds taking overMMNL absent for 63369 secs; Foregrounds taking overMMNL absent for 63369 secs; Foregrounds taking overTue Nov 21 08:14:17 2017ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_21608.trc.Tue Nov 21 08:25:31 2017ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_21608.trc.

在alert日志里面會自動把死鎖信息給列出來，說死鎖發生了

如：

Tue Nov 21 08:25:31 2017ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_21608.trc.

在信息里面可以得到死鎖對應的trc文件

/u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_21608.trc