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';