How can I kill all sessions connecting to my oracle database?
This answer is heavily influenced by a conversation here: http://www.tek-tips.com/viewthread.cfm?qid=1395151&page=3
ALTER SYSTEM ENABLE RESTRICTED SESSION;begin for x in ( select Sid, Serial#, machine, program from v$session where machine <> 'MyDatabaseServerName' ) loop execute immediate 'Alter System Kill Session '''|| x.Sid || ',' || x.Serial# || ''' IMMEDIATE'; end loop; end;
I skip killing sessions originating on the database server to avoid killing off Oracle's connections to itself.
Before killing sessions, if possible do
ALTER SYSTEM ENABLE RESTRICTED SESSION;
to stop new sessions from connecting.