Oracle session
参考网址:http://www.oracle-base.com/articles/misc/killing-oracle-sessions.php
http://junsansi.itpub.net/post/29894/292558
http://blog.csdn.net/tianlesoftware/article/details/7412555
http://blog.csdn.net/tianlesoftware/article/details/7417058
1、查询用户相关的session的ID和session的序列号,这里没有显示系统用户的,因为系统用户在V$SESSION的表里面的
USERNAME为空,只可以通过关联ALL_USERS表,通过user#和user_id相关联。
1 SELECT USERNAME || '(' || SID || ',' || SERIAL# || ')' USERNAME, 2 MODULE, 3 ACTION, 4 CLIENT_INFO 5 FROM V$SESSION 6 WHERE MODULE || ACTION || CLIENT_INFO IS NOT NULL
--关联用户表,查询出用户的名字。
1 SELECT AU.USERNAME, V.ACTION, V.SID 2 FROM V$SESSION V 3 LEFT JOIN ALL_USERS AU 4 ON AU.USER_ID = V.USER# 5 ORDER BY AU.USERNAME;
2、查询session视图中的SQL_ID对应的SQL语句:
1 SELECT VS.SQL_TEXT 2 FROM V$SESSION V 3 LEFT JOIN V$SQL VS 4 ON V.SQL_ID = VS.SQL_ID
3、查询当前的锁模式:
当前的用户为空,代表为系统用户,要显示系统用户的名字,可以通过v$session中的user#与all_users的user_id相关联,来显示用户名字:
1 SELECT SESSION_ID "SID", 2 LOCK_TYPE "Lock Type", 3 MODE_HELD "Mode Held", 4 BLOCKING_OTHERS "Blocking?", 5 V.USERNAME, --为空,代表为系统用户 6 V.MACHINE, 7 V.ACTION 8 FROM DBA_LOCKS DL 9 LEFT JOIN V$SESSION V 10 ON V.SID = DL.SESSION_ID
4、查询出相应的统计资料:包括登录次数,回滚次数,提交次数等等:
1 SELECT NAME, VALUE FROM V$SYSSTAT
5、查询出死锁的次数:
1 select name, value 2 from v$sysstat 3 where name = 'enqueue deadlocks';
6、查看被锁定的对象
1 SELECT * FROM V$LOCKED_OBJECT;
可以查询出OBJECT_ID和SESSION_ID分别和V$SESSION和USER_OBJECT关联查询更具体的信息。
1 SELECT * FROM v$session t WHERE t.SID = '139';--根据会话ID查询 2 SELECT * FROM USER_OBJECTS U WHERE U.OBJECT_ID = '61151';--根据OBJECT_ID查询
7、KILL_SESSION
注意:BLOCK表示的是阻塞的
TYPE != 'BACKGROUND'表示防止杀死系统进程
1 SELECT 'alter system kill session ''' || SID || ',' || SERIAL# || ''';' "Deadlock" 2 FROM V$SESSION 3 WHERE SID IN (SELECT SID FROM V$LOCK WHERE BLOCK = 1) 4 AND TYPE != 'BACKGROUND';
8、查询死锁的SQL语句,&sid为上面查出的ID,查询出的死锁的会话ID
1 SELECT s.sid, q.sql_text 2 FROM v$sqltext q, v$session s 3 WHERE q.address = s.sql_address 4 AND s.sid = &sid
9、查看锁住对象,所有者,类型,会话ID
1 SELECT o.owner, o.object_name, o.object_type, s.sid, s.serial# 2 FROM v$locked_object l, dba_objects o, v$session s 3 WHERE l.object_id = o.object_id 4 AND l.session_id = s.sid 5 ORDER BY o.object_id, xidusn DESC
10、查询非系统进程,和进行操作的语句和他的类型和他的事件
1 SELECT v.sql_text, v.sql_fulltext, t.action, t.type, t.event, t.* 2 FROM v$session t 3 LEFT JOIN v$sql v 4 ON v.sql_id = t.sql_id 5 WHERE t.type != 'BACKGROUND'
11、杀掉会话可以采用两种方式:
1 alter system kill session 'sid,serial#' immediate;--使用immediate关键字 2 alter system kill session 'sid,serial#';
注意:
被标记为killed 的进程由PMON 进程kill,但是这个也是有条件的:
PMON will notdelete the session object itself until the client connected to that sessionnotices that it has been killed.
12、使用系统命令杀掉进程:操作系统级别的杀掉会话
windows命令:
To kill the session on the Windows operating system, first identify the session, then substitute the relevant SID
and SPID
values into the following command issued from the command line.
1 C:> orakill ORACLE_SID(数据库实例名) spid(v$process表中获取)
测试:
1 SELECT Spid, Osuser, s.Program 2 FROM V$session s, V$process p 3 WHERE s.Paddr = p.Addr 4 AND s.Sid = 137;
--END