oracle學習筆記 鎖相關視圖及相關操作
我們繼續研究鎖
這節課主要研究鎖的相關視圖，以及鎖的相關操作
主要是研究視圖
然後通過視圖查鎖的問題
oracle對于鎖來講我們沒有什麼太多可操作的地方
但是我們可以通過視圖找出鎖的問題
一）v$transaction視圖
第一個視圖是v$transaction
就是oracle資料庫所有活動的事務數
所有活動的事務每一個活動的事務在這裡有一行
v$transaction
XIDUSN表示目前事務使用的復原段的編号
XIDSLOT說明該事務在復原段頭部的事務表中對應的記錄編号（也可以叫做槽号）
XIDSQN說明序列号
STATUS說明該事務是否為活動的
這是v$transaction視圖的結構
我們去看一下，比如說去做一個實驗
在一個hr使用者的sqlplus中執行
SQL> delete from employees where rownum=;
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鎖
去查一下，v$lock裡面應該有
使用語句
select sid,type,id1,id2,
decode(lmode,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'Exclusive') lock_mode,
decode(request,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'Exclusive') request_mode,block
from v$lock
where sid=;
這個語句本課中用的較多，這裡把它取名為(#A)語句
最後where條件有sid
sid是會話的編号
先查一下我們會話的編号是多少
在我的實驗環境下
在hr使用者的sqlplus中
SQL> select sid from v$mystat where rownum=;
select sid from v$mystat where rownum=
*
ERROR at line :
ORA-: 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,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'Exclusive') lock_mode,
decode(request,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'Exclusive') request_mode,block
from v$lock
where sid=;
SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK
---------- -- ---------- ---------- ------------------- ------------------- ----------
TM Row share None
TM Row share None
TM Row Exclusive None
TM Row share None
TM Row share None
TM Row share None
TM Row share None
TM Row share None
TX Exclusive None
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鎖
再看剛才的結果
應該是這一行
鎖應該是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(/power(,)) as undo_blk#,bitand(262187,to_number('ffff','xxxx')) + 0 as slot#
from dual;
UNDO_BLK# SLOT#
---------- ----------
看結果是4号復原段，復原段事務表中槽位号SLOT#是43
和以前查詢
SQL> select xidusn,xidslot,xidsqn,status from v$transaction;
XIDUSN XIDSLOT XIDSQN STATUS
---------- ---------- ---------- ----------------
4 43 216 ACTIVE
結果是一樣的
剛才這個語句XIDSQN的值216
(#A)語句結果行
直接有了
(#A)語句結果中ID2是覆寫次數
通過
select trunc(262187/power(2,16)) as undo_blk#,bitand(262187,to_number(‘ffff’,’xxxx’)) + 0 as slot#
from dual; 2
這個sql語句
找出來用的哪個復原段
槽位号是多少
然後包括覆寫次數
這三個資訊
也就是(#A)語句結果中TX這一行
ID1列和ID2列可以找到哪個事務
行
132 TM 51857 0 Row Exclusive None 0
是TM鎖
ID1對應的編号51857是對象的編号
這是v$lock
oracle中的表級鎖有
| 鎖定模式 | 鎖定簡稱 | 編碼數值 |
|---|---|---|
| Row Exclusive | RX | 3 |
| Row Shared | RS | 2 |
| Share | S | 4 |
| Exclusive | X | 6 |
| Share Row Exclusive | SRX | 5 |
| NULL | 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
v lock是把會話持有的鎖和請求的鎖全給列出來了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使用者建立的會話
SID
131
老師執行時傳回結果慢
在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=;
select sid from v$mystat where rownum=
*
ERROR at line :
ORA-: 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 :
ORA-: 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同義詞的來源
查詢V$MYSTAT的說明：
SQL> select * from dict where table_name='V$MYSTAT';
TABLE_NAME
------------------------------
COMMENTS
------------------------------
V$MYSTAT
Synonym 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
SQL> 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$MYSTAT
select SID , STATISTIC# , VALUE from GV$MYSTAT where inst_id = USERENV('Instance')
說明有一個固定視圖也叫V$MYSTAT它來源于GV$MYSTAT
經過查找和分析
GV$MYSTAT同樣有一個同名的PUBLIC同義詞和一個固定視圖
而且也有一個關聯的GV_$MYSTAT視圖
PUBLIC同義詞GV$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$MYSTAT
select 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 TABLE
X$KSUMYSTA TABLE
這是固定表，不用再繼續找了
一般使用者使用的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-: object "PUBLIC" of type USER not found in schema "SYS"
ORA-: at "SYS.DBMS_SYS_ERROR", line
ORA-: at "SYS.DBMS_METADATA", line
ORA-: at "SYS.DBMS_METADATA", line
ORA-: at line
no rows selected
是以PUBLIC是一個角色，不是使用者
但是在DBA_ROLES查詢
SQL> select * from DBA_ROLES;
ROLE PASSWORD
------------------------------ --------
CONNECT NO
RESOURCE NO
DBA NO
SELECT_CATALOG_ROLE NO
EXECUTE_CATALOG_ROLE NO
DELETE_CATALOG_ROLE NO
EXP_FULL_DATABASE NO
IMP_FULL_DATABASE NO
RECOVERY_CATALOG_OWNER NO
GATHER_SYSTEM_STATISTICS NO
LOGSTDBY_ADMINISTRATOR NO
AQ_ADMINISTRATOR_ROLE NO
AQ_USER_ROLE NO
GLOBAL_AQ_USER_ROLE GLOBAL
SCHEDULER_ADMIN NO
HS_ADMIN_ROLE NO
AUTHENTICATEDUSER NO
OEM_ADVISOR NO
OEM_MONITOR NO
WM_ADMIN_ROLE NO
JAVAUSERPRIV NO
JAVAIDPRIV NO
JAVASYSPRIV NO
JAVADEBUGPRIV NO
EJBCLIENT NO
JAVA_ADMIN NO
JAVA_DEPLOY NO
CTXAPP NO
XDBADMIN NO
XDBWEBSERVICES NO
OLAP_DBA NO
OLAP_USER NO
MGMT_USER NO
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# = 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 SESSION
SQL> grant CREATE ANY VIEW to CONNECT;
Grant succeeded.
SQL> select ROLE, PRIVILEGE from role_sys_privs where role='CONNECT';
ROLE PRIVILEGE
------------------------------ ----------------------------------------
CONNECT CREATE ANY VIEW
CONNECT 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 VIEW
HR UNLIMITED TABLESPACE
HR CREATE DATABASE LINK
HR CREATE SEQUENCE
HR CREATE SESSION
HR ALTER SESSION
HR CREATE SYNONYM
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 :
ORA-: role 'SYS' does not exist
進一步說明PUBLIC是個角色
再查HR使用者的權限
HR的系統權限
SQL> select * from dba_sys_privs where grantee = 'HR';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
HR CREATE VIEW NO
HR UNLIMITED TABLESPACE NO
HR CREATE DATABASE LINK NO
HR CREATE SEQUENCE NO
HR CREATE SESSION NO
HR ALTER SESSION NO
HR CREATE SYNONYM NO
rows selected.
HR的角色權限
SQL> select * from dba_role_privs where grantee = 'HR';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
HR RESOURCE NO YES
HR 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 NO
PUBLIC 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=;
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,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'Exclusive') lock_mode,
decode(request,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'Exclusive') request_mode,block
from v$lock
where sid=;
SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK
---------- -- ---------- ---------- ------------------- ------------------- ----------
TM Row Exclusive None
TX Exclusive None
從結果看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=;
這時的執行被卡住
我們再去查一下這裡是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,block
from v$lock
where sid in(139,145)
order by sid;
查的是v$lock
看看這個鎖的狀況
好執行一下，結果
SQL> select sid,type,id1,id2,
decode(lmode,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'Exclusive') lock_mode,
decode(request,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'Exclusive') request_mode,block
from v$lock
where sid in(,)
order by sid;
SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK
---------- -- ---------- ---------- ------------------- ------------------- ----------
TM Row Exclusive None
TX Exclusive None
TM Row Exclusive None
TX None Exclusive
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這個事務鎖住了
記住145被139鎖住了表示這麼個意思
然後我們看139的TX這行
BLOCK的值是1
說明阻塞了别人
阻塞了145
而145 TX的REQUEST_MODE是Exclusive
它正在請求Exclusive鎖，也就是被鎖住了
通過這個我們看到一些問題
但是我們知道這個鎖出現這個問題
也不見得有問題
因為鎖住很正常
139一旦送出以後
145馬上就擷取到這個鎖了
2）關于等待鎖中的ID1和ID2
另外從結果我們可能發現一個問題
TX Exclusive None
TX None Exclusive
我們看到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鎖
這條資訊的ID1和ID2的資訊是它本身事務的資訊
148會話還請求一個TX鎖
TX None Exclusive
這條資訊的ID1和ID2的資訊是148正在請求的被請求鎖的事務的資訊
這裡正在請求150正在持有的鎖
是以這行的ID1和ID2列出了150事務的資訊
有一行
這行的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=;
暫時無傳回值
也被鎖住
這裡是136
現在是139 145 和136操作
139應該把145和136鎖住了
再去查一個語句
select sid,type,
decode(request,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'Exclusive')
request_mode
from v$enqueue_lock
where sid in(,);
根據分析兩個被鎖住的是145和136
查的是v$enqueue_lock
這裡面它會隻是把誰被鎖住了誰給列出來
請求鎖的被列出來
執行一下
這裡通路的是v$enqueue_lock
執行結果
SQL> select sid,type,
decode(request,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'Exclusive')
request_mode
from v$enqueue_lock
where sid in(,);
SID TY REQUEST_MODE
---------- -- -------------------
TX Exclusive
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,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'Exclusive') lock_mode,
decode(request,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'Exclusive') request_mode,block
from v$lock
where sid in(,,)
order by sid;
SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK
---------- -- ---------- ---------- ------------------- ------------------- ----------
TM Row Exclusive None
TX Exclusive None
TM Row Exclusive None
TX None Exclusive
TM Row Exclusive None
TX None Exclusive
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還在等待
139會話中的事務仍然沒有開始
不管是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,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'Exclusive') lock_mode,
b.ctime as time_held,c.sid as waiter_sid,
decode(c.request,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'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 =
order 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,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'Exclusive') lock_mode,
b.ctime as time_held,c.sid as waiter_sid,
decode(c.request,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'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 =
order by time_held, time_waited;
BLOCKER_SID SERIAL# BLOCKER_USERNAME TY LOCK_MODE TIME_HELD WAITER_SID REQUEST_MODE TIME_WAITED
----------- ---------- ------------------------------ -- ------------------- ---------- ---------- ------------------- -----------
HR TX Exclusive Exclusive
HR TX Exclusive Exclusive
前兩個字段
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,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'Exclusive') lock_mode,
b.ctime as time_held,c.sid as waiter_sid,
decode(c.request,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'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 =
order by time_held, time_waited;
BLOCKER_SID SERIAL# BLOCKER_USERNAME TY LOCK_MODE TIME_HELD WAITER_SID REQUEST_MODE TIME_WAITED
----------- ---------- ------------------------------ -- ------------------- ---------- ---------- ------------------- -----------
HR TX Exclusive Exclusive
HR TX Exclusive Exclusive
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=;
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,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'Exclusive') lock_mode,
b.ctime as time_held,c.sid as waiter_sid,
decode(c.request,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'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 =
order by time_held, time_waited;
BLOCKER_SID SERIAL# BLOCKER_USERNAME TY LOCK_MODE TIME_HELD WAITER_SID REQUEST_MODE TIME_WAITED
----------- ---------- ------------------------------ -- ------------------- ---------- ---------- ------------------- -----------
HR TX Exclusive Exclusive
現在是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=;
rows updated.
SQL> rollback;
Rollback complete.
136會話在145會話復原後得到了執行，最終它也得到了鎖
為了試驗把它也復原了
十）一個事務多個TM鎖
一個事務修改多行
産生一個TX鎖，可以在多個表上産生多個TM鎖
一個事務隻産生一個事務鎖TX鎖
我們在一個事務裡面多執行幾條sql語句
update employees set last_name=last_name||'a' where department_id=;
update departments set department_name='unknow' where department_id=;
update locations set city='unknown' where location_id=;
在一個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=;
rows updated.
更新了employees這個表
第二個語句
它接着更新departments
SQL> update departments set department_name='unknow' where department_id=;
row updated.
都是一個事務裡面的
下面語句是更新locations
SQL> update locations set city='unknown' where location_id=;
row updated.
更新了三個語句
然後我們再使用
select sid,type,id1,id2,
decode(lmode,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'Exclusive') lock_mode,
decode(request,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'Exclusive')request_mode,block
from v$lock
where sid=;
再去查132這個會話它持有鎖的情況
在sys使用者會話中執行結果
SQL> select sid,type,id1,id2,
decode(lmode,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'Exclusive') lock_mode,
decode(request,,'None',,'Null',,'Row share',,'Row Exclusive',,'Share',,'Share Row Exclusive',,'Exclusive')request_mode,block
from v$lock
where sid=;
SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK
---------- -- ---------- ---------- ------------------- ------------------- ----------
TM Row Exclusive None
TM Row Exclusive None
TM Row Exclusive None
TX Exclusive None
一個會話的一個事務它修改了三個表
對三個表産生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
transactions
這個有時候也會碰到一些問題
結果中一個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_limit
where resource_name in('transactions','dml_locks');
R_N C_U M_U I_U
------------------------------ ---------- ---------- --------------------
dml_locks
transactions
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;
R_N C_U M_U I_U
------------------------------ ---------- ---------- --------------------
processes
sessions
enqueue_locks
enqueue_resources
ges_procs
ges_ress
ges_locks
ges_cache_ress
ges_reg_msgs
ges_big_msgs
ges_rsv_msgs
gcs_resources
gcs_shadows
dml_locks
temporary_table_locks UNLIMITED
transactions
branches
cmtcallbk
sort_segment_locks UNLIMITED
max_rollback_segments
max_shared_servers UNLIMITED
parallel_max_servers
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事務修改了一個資源以後在這個資源上加了鎖了
B事務修改了另一個資源後也加了鎖
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; 2
1 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; 2
1 row updated.
把101給鎖住了
A裡面把100鎖住了
B把101鎖住了
然後session1想去
SQL> update employees set last_name=last_name||'c' where employee_id=;
想去鎖B鎖住的資源
已被B鎖住了
這時A被B鎖住了
然後session2中
SQL> update employees set last_name=last_name||'d' where employee_id=;
也在等待鎖
B又被A鎖住了
形成一個死循環了
這時在A裡面出現
SQL> update employees set last_name=last_name||'c' where employee_id=;
update employees set last_name=last_name||'c' where employee_id=
*
ERROR at line :
ORA-: 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=;
row updated.
把第二個會話也復原
SQL> rollback;
Rollback complete.
當死循環發生的時候會做幾件事情
第一個oracle自動的對死鎖自動的檢測
而且還能快速檢測
而且把其中一個事務給復原
但隻是復原部分sql語句
2）死鎖的資訊
同時當死鎖發生的時候
會出現一件很重要的事情
oracle會記錄下死鎖的資訊
死鎖發生的時候
[oracle@redhat4 bdump]$ pwd
/u01/app/oracle/admin/jiagulun/bdump
在這個目錄裡面
[[email protected] bdump]$ ls
alert_jiagulun.log jiagulun_lgwr_13577.trc jiagulun_mmnl_6638.trc
jiagulun_cjq0_13651.trc jiagulun_lgwr_13643.trc jiagulun_p000_6646.trc
jiagulun_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.log
Mon Apr ::
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION =
LICENSE_SESSIONS_WARNING =
Shared memory segment for instance monitoring created
.
.
.
Tue Nov ::
MMNL absent for secs; Foregrounds taking over
MMNL absent for secs; Foregrounds taking over
MMNL absent for secs; Foregrounds taking over
Tue Nov ::
ORA-: Deadlock detected. More info in file /u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_21608.trc.
Tue Nov ::
ORA-: Deadlock detected. More info in file /u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_21608.trc.
在alert日志裡面會自動把死鎖資訊給列出來
說死鎖發生了
如：
Tue Nov ::
ORA-: 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
就是死鎖的具體資訊
這個trc檔案老師以後會給大家去分析
本人自己打開看了看，就看懂了開頭的一點
主要内容老師不給講真的看不明白！
這就是關于死鎖的一些情況
死鎖有關于oracle的排錯
這裡面就不講太多了
隻是告訴大家為什麼産生死鎖
oracle怎麼處理
這就是講的oracle的鎖的一些情況
2017年12月12日
文字：韻筝
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)