oracle学习笔记 锁相关视图及相关操作
我们继续研究锁
这节课主要研究锁的相关视图，以及锁的相关操作
主要是研究视图
然后通过视图查锁的问题
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锁
去查一下，v$lock里面应该有
使用语句
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 existhr用户无权访问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是覆盖次数
通过
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=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同义词的来源
查询V$MYSTAT的说明：
SQL> select * from dict where table_name='V$MYSTAT';
TABLE_NAME
------------------------------
COMMENTS
------------------------------
V$MYSTAT
Synonym for V_$MYSTATV$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$mystatTEXT字段是这个视图的定义
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 9999
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 4294951930 TABLE 33
X$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 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
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
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 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
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 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
7 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=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,block
2 3 from v$lock
where sid=139; 4
SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK
---------- -- ---------- ---------- ------------------- ------------------- ----------
139 TM 51857 0 Row Exclusive None 0
139 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,block
from v$lock
where 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,block
from 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 0139和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
另外从结果我们可能发现一个问题
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 0148会话本身持有一个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 0
6 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 0132事务释放锁后
第一个排队的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,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 = 1
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,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 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 = 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_waited
from 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 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 = 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,block
from v$lock
where 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 748
transactions 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_limit
where resource_name in('transactions','dml_locks'); 2 3
R_N C_U M_U I_U
------------------------------ ---------- ---------- --------------------
dml_locks 3 48 748
transactions 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; 2
R_N C_U M_U I_U
------------------------------ ---------- ---------- --------------------
processes 27 37 150
sessions 31 42 170
enqueue_locks 13 22 2300
enqueue_resources 17 38 968
ges_procs 0 0 0
ges_ress 0 0 0
ges_locks 0 0 0
ges_cache_ress 0 0 0
ges_reg_msgs 0 0 0
ges_big_msgs 0 0 0
ges_rsv_msgs 0 0 0
gcs_resources 0 0 0
gcs_shadows 0 0 0
dml_locks 3 48 748
temporary_table_locks 0 0 UNLIMITED
transactions 2 9 187
branches 0 0 187
cmtcallbk 0 2 187
sort_segment_locks 0 1 UNLIMITED
max_rollback_segments 11 11 187
max_shared_servers 1 1 UNLIMITED
parallel_max_servers 0 2 40
22 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=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]$ 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 11 13:38:53 2016
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
.
.
.
Tue Nov 21 06:45:15 2017
MMNL absent for 63369 secs; Foregrounds taking over
MMNL absent for 63369 secs; Foregrounds taking over
MMNL absent for 63369 secs; Foregrounds taking over
Tue Nov 21 08:14:17 2017
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_21608.trc.
Tue Nov 21 08:25:31 2017
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_21608.trc.
在alert日志里面会自动把死锁信息给列出来
说死锁发生了
如：
Tue Nov 21 08:25:31 2017
ORA-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
就是死锁的具体信息
这个trc文件老师以后会给大家去分析
本人自己打开看了看，就看懂了开头的一点
主要内容老师不给讲真的看不明白！
这就是关于死锁的一些情况
死锁有关于oracle的排错
这里面就不讲太多了
只是告诉大家为什么产生死锁
oracle怎么处理
这就是讲的oracle的锁的一些情况
2017年12月12日
文字：韵筝
扫一扫
956
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
