查询哪些锁.docx
《查询哪些锁.docx》由会员分享，可在线阅读，更多相关《查询哪些锁.docx（23页珍藏版）》请在冰点文库上搜索。
查询哪些锁
锁
清楚缓存altersystemflushbuffer_cache清楚缓存
createuseruser_nameidentifiedby密码
createuseruser_nameidentifiedby密码defaulttablespacetablespace_nametemporarytemp_name;指定用户默认的表空间指定临时表空间一般要指定
alteruseruser_nameaccountunlock解锁
grenttouser_name权限C
dropuserusernamecascade；（删除用户和用户下的所有对象，注意关键值cascade）
createuserusernameidentifiedbypasswddefaulttablespaceuserstemporary创建user使用表空间
锁的概述：
.数据库较之excel之类的东西最大的一个不同,就是支持并发.而锁,则是实现数据库并发控制的一个非常重要的技术.对于一张表来说,它分为两部分,一部分是数据,一部分是它的元数据（也就是我们平时说说的相关数据字典）,我们在删除表（droptable）截断表（truncatetable）的时候是没有删除数据的,是修改数据字典,也就是元数据.
所以我们保护一张表,既要保护数据,又要保护元数据.因此,锁可以分为保护元数据的锁--TM锁,和保护数据的锁--TX（表级锁和行级锁）.
可以大概的得知两种锁的信息.TM是同步访问对象用的,TX是和事务有关的.
论:
当对表进行创建索引操作时,会伴随出现LMODE=4的S锁.根据上面的各种操作所对应的锁的表格,发现S锁和任何dml操作都是冲突的!
我们对此就要非常注意了,尤其是在生产上,当在一个很大的表上进行索引创建的时候,任何对该表的DML操作都会被夯住!
!
!
//查询导致死锁的用户和软件还有T-SQL语句,记录时间、计算机名称
selectusername,sql_text,lockwait,status,machine,program,logon_time
fromv$session,v$sql
wherev$session.SQL_ID=v$sql.SQL_ID
orderbylogon_timedesc
【注释】：
通过sql_Text列,您可以查看到被锁定的表是谁
--//根据用户分组会话
selectt.USERNAME,count（*）fromv$sessiontgroupbyt.USERNAME;
--//查找某一用户正在执行的sql_id值
select*fromv$sessiontwheret.USERNAME='BUDGET'andt.SQL_IDisnotnull;
--//查找对应sql_id的对应sql语句
selectm.SQL_TEXTfromv$sessiont,v$sqlareamwheret.USERNAME='BUDGET'andt.sql_id=m.SQL_ID;
--//
select*fromv$sqlareatwheret.SQL_ID='g92sy7kwt6mrq';
---//查询死锁对象
select*fromv$locked_object;
查询哪些锁
（1）
selectb.object_name,a.*fromv$locked_objecta,dba_objectsbwherea.OBJECT_ID=b.object_id;
selectsid,serial#fromv$sessionwheresid=seesion_id;
（2）
selectobject_name,c.sid,c.serial#fromv$locked_objecta,dba_objectsb,v$sessioncwherea.object_id=b.object_idanda.session_id=c.sid;
杀掉会话
altersystemkillsession'sid,serial#';
altersystemkillsession'704,54269';
杀掉会话：
----方法一：
SQL>SELECTpid,spidFROMV$PROCESSWHEREADDRIN（SELECTPADDRFROMV$SESSIONwhereusername='SYS'）;
PIDSPID
----------------------
1713657
ps-ef|grep13657
kill-913657
---方法二：
SQL>SELECTsid,serial#FROMV$SESSIONWHEREsidin（SELECTdistinctsidFROMV$MYSTAT）;
SIDSERIAL#
--------------------
163713894
altersystemdisconnectsession'1637,13894'immediate;
查看是否kill掉
selectusername,statusfromv$sessionwheresid=524;
selectused_ublkfromv$transaction;
-------——————————————————————————-
KillSession经验总结
在Oracle的日常维护中，经常出现以下两种情况需要我们DBAkillsession：
App应用能及时跑完。
2.App的应用跑到一半，突然决定不跑了，需要DBA把他们的sessionkill掉。
以下是我经常采用的killsession的方法：
1.在数据库层面用语句altersystemkill'sid,serial#'
2.在某些特殊情况下，方法1无法使用，便在OS层面执行kill-9
采用以上方法经常碰到的问题是：
对于那些已经跑了很久的session，不是一下子就能kill掉的，有时要等很久，此时就只能傻傻的在那等，当App那边的人问“session消失了吗？
如果没有消失，还要等多久？
”等等之类的问题的时候，总是无法给他们确切的
1.altersystemkill'sid,serial#'这条语句会做什么？
每当执行以下语句时：
A105024@O02DMS1>altersystemkillsession'524,24148';
Systemaltered.
总是能很快的返回Systemaltered的结果，很多人误以为此时session已经被killed了，其实不然，该语句只是发出一个命令，告诉这个session：
你被killed掉了，如果此时session为空闲状态，它可以马上结束，但是如果此时session正在做一个事务，它在结束前需要将事务回滚，并释放锁。
因此，对于那些需要长时间回滚才能被kill掉的session来说，在执行altersystemkillsession之后，它的状态变成KILLED，比
如：
selectusername,statusfromv$sessionwheresid=524;
SIDSTATUS
-------------------------
524KILLED
2.KILLED状态的session还要多少时间才能消失？
如果去评估KILLED状态的session还要多少时间才能消失呢？
我们可以查询v$transcation中的USED_UBLK，比如used_ublks当前值为500，1分钟后剩下400，那么你可以估计该session大概还需要400/（500-400）=4分钟的时间才能结束，以下是一个实际中的例子
：
selectused_ublkfromv$transaction;
USED_UBLK
----------
2262
1rowselected.
SQL>/
USED_UBLK
----------
1430
1rowselected.
SQL>/
USED_UBLK
----------
1103
1rowselected.
一直监控USED_UBLK的值，只要它不等于0，该session还是处于KILLED的状态：
selectusername,statusfromv$sessionwheresid=524;
SIDSTATUS
-------------------------
524KILLED
selectused_ublkfromv$transaction;
USED_UBLK
----------
1048
1rowselected.
SQL>/
USED_UBLK
----------
489
1rowselected.
SQL>/
norowsselected
没有USED_UBLK返回，说明回滚已经结束。
3.为什么USED_UBLK的值已经为0了，session还是显示killed的状态？
这是因为必需得等到client端收到ORA-00028错误之后，session才会消失，换句话说，即使session已经回滚完毕，但如果client端还没有确认，session还是保持在killed的状态，如：
selectused_ublkfromv$transaction;
norowsselected
虽然USED_UBLK已经为0了，但是session还是killed的状态：
selectusername,statusfromv$sessionwheresid=524;
SIDSTATUS
-------------------------
524KILLED
在client端，尝试任何执行语句，都会出现ORA-00028：
SQL>selectinstance_namefromv$instance;
selectinstance_namefromv$instance
*
ERRORatline1:
ORA-00028:
yoursessionhasbeenkilled
此时session才会消失：
selectusername,statusfromv$sessionwheresid=524;
norowsselected
4.kill-9与altersystemkillsession的区别
altersystemkillsession的原理是让session自己把自己kill掉，也就是session自己回滚；而kill-9是让pmon清理session。
kill-9的速度看起来比altersystemkillsession要快一些，这是因为pmon会做一些并行的处理，让回滚更快一些，
但是kill-9不建议使用，因为：
1.在OS层面直接kill进程，存在未知的风险；
2.容易误操作，如果万一把Oracle的关键进程（如smon）kill掉，会造成数据库宕机的事故；
3.无法监控事务回滚进度。
所以，不到万不得已的情况下，尽量不要用kill-9。
————————————————————————————————
查看日志情况
COLgroup#FOR999999;
COLmember#FOR999;
COL"logfilepath"FORa50;
COL"MB"FOR999,999,999;
SELECTl.thread#,
l.group#,
l.membersAS"member#",
lf.MEMBERAS"logfilepath",
bytes/1024/1024"MB",
sequence#,
l.status
FROMv$logl,v$logfilelf
WHEREl.group#=lf.group#
ORDERBYthread#,group#,members;
————————————————————-——————————————————
1、查看锁
SELECTp.spid,
a.serial#,
c.object_name,
b.session_id,
b.oracle_username,
b.os_user_name
FROMv$processp,v$sessiona,v$locked_objectb,all_objectsc
WHEREp.addr=a.paddr
ANDa.process=b.process
ANDc.object_id=b.object_id
2.查看是哪个进程锁的
SELECTsid,serial#,username,osuserFROMv$sessionwhereosuser='tangpj'
3.杀掉这个进程altersystemkillsession'sid,serial#';
———————————————————————————————————————————
可在PL/SQL中用如下SQL语句来查询当前数据库中哪些表被锁住了，并且是哪些用户来锁的这些表：
SELECT
A.OWNER,
A.OBJECT_NAME,
B.XIDUSN,
B.XIDSLOT,
B.XIDSQN,
B.SESSION_ID,
B.ORACLE_USERNAME,
B.OS_USER_NAME,
B.PROCESS,
B.LOCKED_MODE,
C.MACHINE,
C.STATUS,
C.SERVER,
C.SID,
C.SERIAL#,
C.PROGRAM
FROM
ALL_OBJECTSA,
V$LOCKED_OBJECTB,
SYS.GV_$SESSIONC
WHERE
A.OBJECT_ID=B.OBJECT_ID
ANDB.PROCESS=C.PROCESS
ORDERBY1,2
--------------------------------------------------
selectA.sid,
b.serial#,
decode（A.type,
'MR',
'MediaRecovery',
'RT',
'RedoThread',
'UN',
'UserName',
'TX',
'Transaction',
'TM',
'DML',
'UL',
'PL/SQLUserLock',
'DX',
'DistributedXaction',
'CF',
'ControlFile',
'IS',
'InstanceState',
'FS',
'FileSet',
'IR',
'InstanceRecovery',
'ST',
'DiskSpaceTransaction',
'TS',
'TempSegment',
'IV',
'LibraryCacheInvalida-tion',
'LS',
'LogStartorSwitch',
'RW',
'RowWait',
'SQ',
'SequenceNumber',
'TE',
'ExtendTable',
'TT',
'TempTable',
'Unknown'）LockType,
c.object_name,
b.username,
b.osuser,
decode（a.lmode,
0,
'None',
1,
'Null',
2,
'Row-S',
3,
'Row-X',
4,
'Share',
5,
'S/Row-X',
6,
'Exclusive',
'Unknown'）LockMode,
B.MACHINE,
D.SPID
fromv$locka,v$sessionb,all_objectsc,V$PROCESSD
wherea.sid=b.sid
anda.typein（'TM','TX'）
andc.object_id=a.id1
ANDB.PADDR=D.ADDR
-----------------------------------------------------------
SELECT
A.OWNER,--OBJECT所属用户
A.OBJECT_NAME,--OBJECT名称（表名）
B.XIDUSN,
B.XIDSLOT,
B.XIDSQN,
B.SESSION_ID,--锁表用户的session
B.ORACLE_USERNAME,--锁表用户的Oracle用户名
B.OS_USER_NAME,--锁表用户的操作系统登陆用户名
B.PROCESS,
B.LOCKED_MODE,
C.MACHINE,--锁表用户的计算机名称（例如：
WORKGROUP\UserName）
C.STATUS,--锁表状态
C.SERVER,
C.SID,
C.SERIAL#,
C.PROGRAM--锁表用户所用的数据库管理工具（例如：
ob9.exe）
FROM
ALL_OBJECTSA,
V$LOCKED_OBJECTB,
SYS.GV_$SESSIONC
WHERE
A.OBJECT_ID=B.OBJECT_ID
ANDB.PROCESS=C.PROCESS
ORDERBY1,2同时可用如下命令来kill掉当前锁表的项：
————————————————————————————————
//lock_date是被锁住时间，如果为空证明这个用户没有被锁住
selectusername,lock_datefromdba_userswhereusername='GFMIS';//GFMIS是数据库的用户
//查看Oracle数据库默认允许重复连接多少次，默认是10次
select*fromdba_profilesWHEREresource_name='FAILED_LOGIN_ATTEMPTS'ANDPROFILE='DEFAULT';
//修改Oracle数据库默认连接数为100个用户
alterprofiledefaultlimitFAILED_LOGIN_ATTEMPTS100;
//设置无限制
alterprofileDEFAULTlimitFAILED_LOGIN_ATTEMPTSUNLIMITED;
//给用户解锁
ALTERUSERgfmisACCOUNTUNLOCK;
——————————————————————————
---oracle查看被锁的表和被锁的进程，杀掉进程
--1.查看被锁的表
SELECTp.spid,a.serial#,c.object_name,b.session_id,b.oracle_username,b.os_user_name
FROMv$processp,v$sessiona,v$locked_objectb,all_objectsc
WHEREp.addr=a.paddr
ANDa.process=b.process
ANDc.object_id=b.object_id
--2.查看是哪个进程锁的
SELECTsid,serial#,username,status,osuserFROMv$sessionwhereserial#='5002'
SELECTsid,serial#,username,status,osuserFROMv$sessionwhereserial#='3789'
--3.杀掉这个进程
altersystemkillsession'sid,serial#';
altersystemkillsession'269,3789';
---查看锁和等待:
coluser_nameformata10
colownerformata10
colobject_nameformata15
colsidformat999999
colserial#format999999
colspidformata6
select/*+rule*/lpad（'',decode（l.xidusn,0,3,0））||l.oracle_usernameuser_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#,p.spid
fromv$locked_objectl,dba_objectso,v$sessions,v$processp
wherel.object_id=o.object_idandl.session_id=s.sidands.paddr=p.addr
orderbyo.object_id,xidusndesc;
查看当前的session
selectusername,sid,serial#fromv$sessionwhereusernameisnotnull
--SERIAL#:
SID有可能会重复，当两个session的SID重复时，SERIAL#用来区别session
查询当前session的sid，serial#
selectsid,serial#,statusfromv$sessionwhereaudsid=userenv（'sessionid'）;
---查看当前用户的spid:
selectspidfromv$processp,v$sessionswheres.audsid=userenv（'sessionid'）ands.paddr=p.addr;
selectspidfromv$processpjoinv$sessions
升级会员 