oracle删除不活动session,删除Oracle非活动Session的方法

--1.查找运行时间超过9小时的当前Win.exe程序的非活动Session

select SID,Serial#, SQL_ID,MACHINE,terminal,SECONDS_IN_WAIT,client_info,status,program,module,round(SECONDS_IN_WAIT/3600) as waithour from v$session

where status<>'ACTIVE' and program='Win.exe' and seconds_in_wait>9*60*60

order by seconds_in_wait desc

--2.生成删除的语句

SELECT 'alter system kill session ''' || ta.sid || ',' || ta.serial# || ''';'  KillSql,

'alter system disconnect session ''' || ta.sid || ',' || ta.serial# || ''' immediate;' DisConnSql,

'host orakill ' || tc.instance_name || ' ' || tb.spid OrakillSql,

tb.spid,

ta.osuser,

tb.program,

ta.terminal,

ta.program

FROM v$session ta, v$process tb, v$instance tc

WHERE tb.addr = ta.paddr

and  ta.status<>'ACTIVE' and ta.program='Win.exe' and ta.seconds_in_wait>9*60*60

--3.执行语句，前两条语句在PL/SQL中之行，后一条语句在系统的命令窗口以dos窗口中执行