查询哪些锁.docx
- 文档编号:1953805
- 上传时间:2023-05-02
- 格式:DOCX
- 页数:23
- 大小:22.60KB
查询哪些锁.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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 查询 哪些