SELECT /*+ rule */ s.username, decode(l.type,'TM','TABLE LOCK', 'TX','ROW LOCK', NULL) LOCK_LEVEL, o.owner,o.object_name,o.object_type, s.sid as sid,s.serial# as serial#,s.terminal,s.machine,s.program,s.osuser FROM v$session s,v$lock l,dba_objects o WHERE l.sid = s.sid AND l.id1 = o.object_id(+) AND s.username is NOT NULL --kill session语句alter system kill session'sid,serial#' immediate; alter system kill session'10,46004' immediate; --以下几个为相关表 SELECT * FROM v$lock; SELECT * FROM v$sqlarea; SELECT * FROM v$session; SELECT * FROM v$process ; SELECT * FROM v$locked_object; SELECT * FROM all_objects; SELECT * FROM v$session_wait; --1.查出锁定object的session的信息以及被锁定的object名 SELECT l.session_id sid, s.serial# as serial#, l.locked_mode,l.oracle_username, l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time FROM v$locked_object l, all_objects o, v$session s WHERE l.object_id = o.object_id AND l.session_id = s.sid ORDER BY sid, s.serial# ; --2.查出锁定表的session的sid, serial#,os_user_name, machine name, terminal和执行的语句 --比上面那段多出sql_text和action SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s.user#, l.os_user_name,s.machine, s.terminal,a.sql_text, a.action FROM v$sqlarea a,v$session s, v$locked_object l WHERE l.session_id = s.sid AND s.prev_sql_addr = a.address ORDER BY sid, s.serial#; --3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal,锁的type,mode SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine, s.terminal, s.logon_time, l.type FROM v$session s, v$lock l WHERE s.sid = l.sid AND s.username IS NOT NULL ORDER BY sid; --这个语句将查找到数据库中所有的DML语句产生的锁,还可以发现, --任何DML语句其实产生了两个锁,一个是表锁,一个是行锁。 --杀锁命令 alter system kill session 'sid,serial#' SELECT /*+ rule */ s.username, decode(l.type,'TM','TABLE LOCK', 'TX','ROW LOCK', NULL) LOCK_LEVEL, o.owner,o.object_name,o.object_type, s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser FROM v$session s,v$lock l,dba_objects o WHERE l.sid = s.sid AND l.id1 = o.object_id(+) AND s.username is NOT NULL --以上查询结果是一个树状结构,如果有子节点,则表示有等待发生。 --如果想知道锁用了哪个回滚段,还可以关联到V$rollname,其中xidusn就是回滚段的USN col user_name format a10 col owner format a10 col object_name format a10 col object_type format a10 --如果发生了锁等待,我们可能更想知道是谁锁了表而引起谁的等待 --以下的语句可以查询到谁锁了表,而谁在等待。 SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name, o.owner,o.object_name,o.object_type,s.sid,s.serial# FROM v$locked_object l,dba_objects o,v$session s WHERE l.object_id=o.object_id AND l.session_id=s.sid ORDER BY o.object_id,xidusn DESC
===================================
SYS >select sid,serial#,status,server,inst_id from gv$session where username='APP';
SID SERIAL# STATUS SERVER INST_ID
---------- ---------- -------- --------- ----------
20 1925 INACTIVE DEDICATED 1
不加immedate,kill session后查询gv$session,该会话记录STATUS是KILLED。
SYS >alter system kill session '20,1925,@1';
SYS >select sid,serial#,status,server,inst_id from gv$session where username='APP';
SID SERIAL# STATUS SERVER INST_ID
---------- ---------- -------- --------- ----------
20 1925 KILLED PSEUDO 1
在用户app的会话中操作,提示如下:
APP >select * from tab;
select * from tab
*
ERROR at line 1:
ORA-00028: your session has been killed
再查询gv$session,该会话记录已经没有了。
SYS >select sid,serial#,status,server,inst_id from gv$session where username='APP';
no rows selected
用app用户重新登录,再加上immediate子句操作:
SYS >select sid,serial#,status,server,inst_id from gv$session where username='APP';
SID SERIAL# STATUS SERVER INST_ID
---------- ---------- -------- --------- ----------
243 15622 INACTIVE DEDICATED 1
SYS >alter system kill session '243,15622,@1' immediate;
再查询gv$session
SYS >select sid,serial#,status,server,inst_id from gv$session where username='APP';
no rows selected
这是可以看到gv$session已经没有记录了。
而app用户查询,直接提示连接断开了。
APP >select * from tab;
select * from tab
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 10189
Session ID: 243 Serial number: 15622
Whether or not the session has an ongoing transaction, Oracle Database does not
recover the entire session state until the session user issues a request to the session and
receives a message that the session has been terminated.
IMMEDIATE Specify IMMEDIATE to instruct Oracle Database to roll back ongoing
transactions, release all session locks, recover the entire session state, and return
control to you immediately.
根据oracle的文档,在没有immediate字句情况下,oracle终结会话后,直到会话用户发起请求并提示会话终结后,才会恢复该会话状态。而immediate子句的作用是终结会话后,马上恢复该会话状态,所以前面例子操作后查询gv$session,已经没有该会话状态。
相关推荐
Oracle的锁表与解锁
oracle锁表和解锁语句示例。
orale检查锁表,以及对表进行解锁的方式说明
oracle查询锁表和解锁。oracle在操作的过程中经常会遇到锁表的情况,一般能够用kill命令消除。
数据库死锁的概念, 所谓死锁,是指两个会话,每个会话都持有另外一个会话想要的资源,因争夺资源而造成...对于锁死的会话,我们可以直接删掉该会话,等事物回滚完成,也可以找出锁死进程的spid,从服务器中删掉该进程。
oracle解锁,死锁oracle解锁,死锁oracle解锁,死锁oracle解锁,死锁oracle解锁,死锁oracle解锁,死锁oracle解锁,死锁oracle解锁,死锁oracle解锁,死锁oracle解锁,死锁oracle解锁,死锁
oracle 解锁表,可以用此字句,对表进行解锁,但是要有相应的权限
Oracle数据库操作中,我们有时会用到锁表查询以及解锁和kill进程等操作,那么这些操作是怎么实现的呢?
oracle解锁语句,常用解锁语句,经过测试实用;oracle解锁语句,常用解锁语句,经过测试实用。
B.SESSION_ID 锁表SESSION_ID, B.ORACLE_USERNAME 锁表用户名, decode(D.type, 'XR', 'NULL', 'RS', 'SS(Row-S)', 'CF', 'SS(Row-S)', 'TM', 'TABLE LOCK', 'PW', 'TABLE LOCK', 'TO', 'TABLE LOCK',...
ORACLE解除锁表,能够让被锁的表,强制解锁。
oracle查锁和解锁命令
针对oracle数据库 解锁方法 ,个人使用 希望大家能够有所帮助
如果你在用oracle数据中某个表时,突然不用了,有可能是某人把你的表给锁住了,现在说说在不知道谁锁的情况下,如何去解锁…… 文档中有详细说明……
ORACLE 如何查询被锁定表及如何解锁释放session
oracle 查看锁表sql 及如何解锁,多给点分,为了下载别的资料。大家相互学习相互进步
select object_name,session_id,os_user_name,Oracle_username,process,locked_mode,status from v$locked_object l, all_objects a where l.object_id=a.object_id;
ORACLE解锁方法的一点资料分享。
解锁oracle对象
oracle 用户被锁定解锁方法