日常session查询和查杀

最新推荐文章于 2022-11-22 18:30:58 发布

原创最新推荐文章于 2022-11-22 18:30:58 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#session #sql #kill #class #user #system

本文深入探讨了SQL查询在数据库管理系统中的应用，包括通过PID、SID查询SQL文本，以及根据SID杀死会话的技术。同时介绍了如何查看和管理与特定会话相关的进程信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

------------------包含v$SQL 根据PID--------------------------------
select p.addr,s.sid,s.serial#,s.osuser,s.program,s.machine,p.spid,p.pid,sq.sql_text
from v$sql sq,v$session s,v$process p
where sq.address=s.sql_address
and s.paddr=p.addr
and p.spid='&pid'

-------------------包含v$sql,根据Sid-------------------------
select p.addr,s.sid,s.serial#,p.spid,p.pid,sq.sql_text
from v$sql sq,v$session s,v$process p
where sq.address=s.sql_address
and s.paddr=p.addr
and s.sid='&sid'
-----------------------------------------------------
select p.addr,s.sid,s.serial#,s.osuser,s.program,s.machine,p.spid,p.pid,sq.sql_id,sq.sql_text
from v$sqlarea sq,v$session s,v$process p
where sq.address=s.sql_address
and s.paddr=p.addr
and sql_id='c4h5p6vn43wzh'
and s.status='ACTIVE'

and s.sid='&sid'
-----------------------不包含v$SQL-----------------------
select p.addr,s.sid,s.serial#,p.spid,p.pid
from v$session s,v$process p
where s.paddr=p.addr
and p.spid='&pid'

select p.addr,s.sid,s.serial#,p.spid,p.pid
from v$session s,v$process p
where s.paddr=p.addr
and s.sid='&sid'
-----------------------根据SID KILL SESSION-----------------------
select s.STATUS,
       s.username,
       s.osuser,
       s.program,
       s.MACHINE,
       s.sid,
       s.serial#,
       'alter system kill session ' || '''' || trim(s.sid) || ',' ||trim(s.serial#) || ''';'
from v$session s
where s.sid ='&sid'
-------------------------------------------------------

select sid,username,event from v$sessionwherestate in('WAITING') and wait_class!='Idle';
select sid,username,event from v$session where state in('WAITING') and wait_class!='Idle';
user_indexes   user_ind_columns
----------------------------根据sid查看sql_id-----------------------------
select p.addr,s.sid,s.serial#,p.spid,p.pid,sq.sql_id,sq.sql_text
from v$sql sq,v$session s,v$process p
where sq.address=s.sql_address
and s.paddr=p.addr
and s.sid='&sid'

select p.addr,s.sid,s.serial#,p.Spid,sq.sql_id,sq.sql_text
from v$sql sq,v$session s,v$process p
where sq.address=s.sql_address
and s.paddr=p.addr
and s.sid='&sid'
-------------------------------------------由SQL_ID查询用户客户端信息------------------------------------
select p.addr,s.username,s.sid,s.serial#,s.osuser,s.program,s.machine,p.spid,p.pid,sq.sql_id,sq.sql_text
from v$sqlarea sq,v$session s,v$process p
where sq.address=s.sql_address
and s.paddr=p.addr
and sq.sql_id='47hmwjysvytmw'
and s.status='ACTIVE'