知識星球
本文主要研究鎖的相關檢視，以及鎖的相關操作，透過檢視查鎖的問題。
一、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和TMLMODE說明已經獲得的鎖定的樣式，以數字編碼表示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,blockfrom v$lockwhere 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,block2 3 from v$lock4 where sid=132;SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK---------- -- ---------- ---------- ------------------- ------------------- ----------132 TM 51852 0 Row share None 0132 TM 51855 0 Row share None 0132 TM 51857 0 Row Exclusive None 0132 TM 51864 0 Row share None 0132 TM 51871 0 Row share None 0132 TM 51889 0 Row share None 0132 TM 51894 0 Row share None 0132 TM 51902 0 Row share None 0132 TX 262187 216 Exclusive None 09 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; 2UNDO_BLK---------- ----------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使用者建立的連線傳回了一個結果
SID134
當前的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") ASselect "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
說明有一個固定檢視也叫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
說明固定檢視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 1no 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 NO33 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") ASselect 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 SYNONYM7 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的系統許可權
= '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 YESSQL> 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使用下麵的陳述句查一下
查詢結果
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,block2 3 from v$lockwhere sid=139; 4SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK---------- -- ---------- ---------- ------------------- ------------------- ----------139 TM 51857 0 Row Exclusive None 0139 TX 458775 238 Exclusive None 0
從結果看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$lock4 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 0139 TX 458775 238 Exclusive None 1145 TM 51857 0 Row Exclusive None 0145 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 1145 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 0148 TM 51857 0 Row Exclusive None 0148 TX 524327 303 Exclusive None 0150 TX 262165 242 Exclusive None 1150 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_modefrom v$enqueue_lockwhere 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_modefrom v$enqueue_lockwhere sid in(145,136); 2 3 4 5SID TY REQUEST_MODE---------- -- -------------------145 TX Exclusive136 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,block4 from v$lock5 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 0132 TX 655370 242 Exclusive None 1135 TM 51857 0 Row Exclusive None 0135 TX 655370 242 None Exclusive 0139 TM 51857 0 Row Exclusive None 0139 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 0135 TX 327683 338 Exclusive None 1139 TM 51857 0 Row Exclusive None 0139 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_waitedfrom 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 = 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_waited6 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 = 17 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 1790139 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 a6 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 = 18 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 4215139 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_waited6 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 = 17 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,block4 from v$lock5 where sid=132;SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK---------- -- ---------- ---------- ------------------- ------------------- ----------132 TM 51857 0 Row Exclusive None 0132 TM 51852 0 Row Exclusive None 0132 TM 51847 0 Row Exclusive None 0132 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_limitwhere 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----------150SQL> 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----------148SQL> 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 2017: Deadlock detected. More info in file /u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_21608.trc.Tue Nov 21 08:25:31 2017: 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
以上就是講的Oracle的鎖的一些情況，分享至此，供大家參考學習，如有幫助，歡迎轉發+在看~
朋友會在“發現-看一看”看到你“在看”的內容