日常session查询和查杀

来源：互联网发布：汉语语法知乎编辑：程序博客网时间：2024/05/17 22:55

------------------包含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'