Oracle session参考网址:http://www./articles/misc/killing-oracle-sessions.php http://junsansi./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 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 |
|