ORACLE:集群oracle查看session的语句
1.查看session可以把status改为ACTIVE和INACTIVE
v$session 和gv$session 只在RAC中区别明显，有g是全局的，rac的两个数据库中的内容，没有是但实例的数据库的，内容不全
rac么？ gv$开头视图是rac全局的，v$开头是本节点的
gv$session比v$session多一个字段而已： INST_ID
select machine ,count(*) from gv$session where logon_time < sysdate -1 and status ='INACTIVE' group by machine；
2.查看目前的session已经正在执行的sql
SELECT S.USERNAME,
S.SID,
S.SERIAL#,
S.INST_ID,
S.EVENT,
S.WAIT_CLASS,
S.SQL_EXEC_START,
S.LOGON_TIME,
S.ACTION,
SQ.SQL_TEXT,
S.MACHINE
FROM GV$SESSION S, GV$SQLAREA SQ
WHERE S.STATUS = 'ACTIVE'
AND S.USERNAME IS NOT NULL
AND S.SQL_ID = SQ.SQL_ID;
3.单节点生成批量kill session的sql
select 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' cmd from v$session
where username is not null
and status = 'ACTIVE'
4.RAC架构生成批量kill session的sql
方法一、在节点一执行
select 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' cmd from v$session
where username is not null
and status = 'ACTIVE'
生成的kill语句，在节点一服务器执行
在节点二执行
select 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' cmd from v$session
where username is not null
and status = 'ACTIVE'
生成的kill语句，在节点二服务器执行
需要分别杀掉
方法二、在一个节点执行
select 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' cmd
from gv$session
where username is not null and to_char(prev_exec_start,'yyyy-mm-dd hh24:mi:ss') < '2021-10-18 10:37:00' and status = 'INACTIVE'
and inst_id=1;
inst_id=1 指定节点，节点二可以用inst_id=2
生成的kill语句，在节点二服务器执行
生成删除指定时间点的KILL session语句
select 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' cmd
from v$session
where username is not null and to_char(prev_exec_start,'yyyy-mm-dd hh24:mi:ss') < '2025-10-5 00:00:00' and status = 'INACTIVE'
生成指定machine批量kill的语句
select 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' cmd
from v$session
where username is not null and MACHINE like 'mes%' and status = 'INACTIVE'
4. 查看某个时间点的active session的数量
select 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' cmd
from v$session
where username is not null and MACHINE like 'mes%' and status = 'INACTIVE'
5. Kill session的方法
ALTER SYSTEM KILL SESSION 'SID,SERIAL#';
例如： ALTER SYSTEM KILL SESSION '5,3248';
