Wednesday, April 3, 2013

Oracle: find out / kill connected non-system users


SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' AS RESULT,
'-- This will kill ' || schemaname || ' session, is occupied by ' || osuser || ', ''' || program
|| ''', started from '''|| machine || ''' at ' || first_load_time as notes
FROM (
  SELECT sid, serial#, schemaname, program, osuser, machine, logon_time, sql_hash_value FROM v$session
  WHERE SCHEMANAME NOT IN ('SYS', 'SYSTEM', 'ORACLE')
  AND STATUS ='ACTIVE'
  AND SID NOT IN (SELECT sys_context('USERENV','SID') FROM dual)
) sel
join v$sql v on (sel.sql_hash_value = v.hash_value);

No comments:

Post a Comment