How can I kill all sessions connecting to my oracle database?
Solution 1
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.
Solution 2
As SYS:
startup force;
Brutal, yet elegant.
Solution 3
I've been using something like this for a while to kill my sessions on a shared server. The first line of the 'where' can be removed to kill all non 'sys' sessions:
BEGIN
FOR c IN (
SELECT s.sid, s.serial#
FROM v$session s
WHERE (s.Osuser = 'MyUser' or s.MACHINE = 'MyNtDomain\MyMachineName')
AND s.USERNAME <> 'SYS'
AND s.STATUS <> 'KILLED'
)
LOOP
EXECUTE IMMEDIATE 'alter system kill session ''' || c.sid || ',' || c.serial# || '''';
END LOOP;
END;
Solution 4
Additional info
Important Oracle 11g changes to alter session kill session
Oracle author Mladen Gogala notes that an @ sign is now required to kill a session when using the inst_id column:
alter system kill session '130,620,@1';
http://www.dba-oracle.com/tips_killing_oracle_sessions.htm
Solution 5
If you want to stop new users from connecting, but allow current sessions to continue until they are inactive, you can put the database in QUIESCE mode:
ALTER SYSTEM QUIESCE RESTRICTED;
From the Oracle Database Administrator's Guide:
Non-DBA active sessions will continue until they become inactive. An active session is one that is currently inside of a transaction, a query, a fetch, or a PL/SQL statement; or a session that is currently holding any shared resources (for example, enqueues). No inactive sessions are allowed to become active...Once all non-DBA sessions become inactive, the ALTER SYSTEM QUIESCE RESTRICTED statement completes, and the database is in a quiesced state
Comments
-
BIBD about 4 years
I need to quickly (and forcibly) kill off all external sessions connecting to my oracle database without the supervision of and administrator.
I don't want to just lock the database and let the users quit gracefully.
How would I script this?