<p>I need to quickly (and forcibly) kill off all external sessions connecting to my oracle database without the supervision of and administrator.</p>

<p>I don't want to just lock the database and let the users quit gracefully.</p>

<p>How would I script this?</p>

[{'answer_id': 55359, 'author': 'BIBD', 'author_id': 685, 'author_profile': 'https://Stackoverflow.com/users/685', 'pm_score': 7, 'selected': True, 'text': '<p>This answer is heavily influenced by a conversation here:  <a href="http://www.tek-tips.com/viewthread.cfm?qid=1395151&amp;page=3" rel="noreferrer">http://www.tek-tips.com/viewthread.cfm?qid=1395151&amp;page=3</a></p>\n\n<pre><code>ALTER SYSTEM ENABLE RESTRICTED SESSION;\n\nbegin     \n    for x in (  \n            select Sid, Serial#, machine, program  \n            from v$session  \n            where  \n                machine &lt;&gt; \'MyDatabaseServerName\'  \n        ) loop  \n        execute immediate \'Alter System Kill Session \'\'\'|| x.Sid  \n                     || \',\' || x.Serial# || \'\'\' IMMEDIATE\';  \n    end loop;  \nend;\n</code></pre>\n\n<p>I skip killing sessions originating on the database server to avoid killing off Oracle\'s connections to itself.</p>\n'}, {'answer_id': 59580, 'author': 'Grrey', 'author_id': 6155, 'author_profile': 'https://Stackoverflow.com/users/6155', 'pm_score': 1, 'selected': False, 'text': "<p><strong>Try trigger on logon</strong></p>\n\n<p>Insted of trying disconnect users you should not allow them to connect.</p>\n\n<p>There is and example of such trigger.</p>\n\n<pre><code>CREATE OR REPLACE TRIGGER rds_logon_trigger\nAFTER LOGON ON DATABASE\nBEGIN\n  IF SYS_CONTEXT('USERENV','IP_ADDRESS') not in ('192.168.2.121','192.168.2.123','192.168.2.233') THEN\n    RAISE_APPLICATION_ERROR(-20003,'You are not allowed to connect to the database');\n  END IF;\n\n  IF (to_number(to_char(sysdate,'HH24'))&lt; 6) and (to_number(to_char(sysdate,'HH24')) &gt;18) THEN\n    RAISE_APPLICATION_ERROR(-20005,'Logon only allowed during business hours');\n  END IF;\n\nEND;\n</code></pre>\n"}, {'answer_id': 60807, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 3, 'selected': False, 'text': '<p>Before killing sessions, if possible do</p>\n\n<pre><code>ALTER SYSTEM ENABLE RESTRICTED SESSION;\n</code></pre>\n\n<p>to stop new sessions from connecting.</p>\n'}, {'answer_id': 234184, 'author': 'Gazmo', 'author_id': 31175, 'author_profile': 'https://Stackoverflow.com/users/31175', 'pm_score': 2, 'selected': False, 'text': '<p>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:</p>\n\n<pre><code>ALTER SYSTEM QUIESCE RESTRICTED;\n</code></pre>\n\n<p>From the <a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/start004.htm" rel="nofollow noreferrer">Oracle Database Administrator\'s Guide</a>:</p>\n\n<blockquote>\n  <p>Non-DBA active sessions will continue\n  until they become inactive. An active\n  session is one that is currently\n  inside of a transaction, a query, a\n  fetch, or a PL/SQL statement; or a\n  session that is currently holding any\n  shared resources (for example,\n  enqueues). No inactive sessions are\n  allowed to become active...Once all\n  non-DBA sessions become inactive, the\n  ALTER SYSTEM QUIESCE RESTRICTED\n  statement completes, and the database\n  is in a quiesced state</p>\n</blockquote>\n'}, {'answer_id': 939765, 'author': 'jon077', 'author_id': 22801, 'author_profile': 'https://Stackoverflow.com/users/22801', 'pm_score': 1, 'selected': False, 'text': '<p>I found the below snippet helpful.  Taken from:  <a href="http://jeromeblog-jerome.blogspot.com/2007/10/how-to-unlock-record-on-oracle.html" rel="nofollow noreferrer">http://jeromeblog-jerome.blogspot.com/2007/10/how-to-unlock-record-on-oracle.html</a></p>\n\n<pre><code>select\nowner||\'.\'||object_name obj ,\noracle_username||\' (\'||s.status||\')\' oruser ,\nos_user_name osuser ,\nmachine computer ,\nl.process unix ,\ns.sid||\',\'||s.serial# ss ,\nr.name rs ,\nto_char(s.logon_time,\'yyyy/mm/dd hh24:mi:ss\') time\nfrom v$locked_object l ,\ndba_objects o ,\nv$session s ,\nv$transaction t ,\nv$rollname r\nwhere l.object_id = o.object_id\nand s.sid=l.session_id\nand s.taddr=t.addr\nand t.xidusn=r.usn\norder by osuser, ss, obj\n;\n</code></pre>\n\n<p>Then ran:</p>\n\n<pre><code>Alter System Kill Session \'&lt;value from ss above&gt;\'\n;\n</code></pre>\n\n<p>To kill individual sessions.</p>\n'}, {'answer_id': 3825265, 'author': 'Gaius', 'author_id': 447514, 'author_profile': 'https://Stackoverflow.com/users/447514', 'pm_score': 4, 'selected': False, 'text': '<p>As SYS:</p>\n\n<pre><code>startup force;\n</code></pre>\n\n<p>Brutal, yet elegant.</p>\n'}, {'answer_id': 6122593, 'author': 'dovka', 'author_id': 555848, 'author_profile': 'https://Stackoverflow.com/users/555848', 'pm_score': 1, 'selected': False, 'text': "<p>To answer the question asked,\nhere is the most accurate SQL to accomplish the job,\nyou can combine it with PL/SQL loop to actually run kill statements:</p>\n\n<pre><code>select ses.USERNAME,\n    substr(MACHINE,1,10) as MACHINE, \n    substr(module,1,25) as module,\n    status, \n    'alter system kill session '''||SID||','||ses.SERIAL#||''';' as kill\nfrom v$session ses LEFT OUTER JOIN v$process p ON (ses.paddr=p.addr)\nwhere schemaname &lt;&gt; 'SYS'\n    and not exists\n    (select 1 \n        from DBA_ROLE_PRIVS \n        where GRANTED_ROLE='DBA' \n            and schemaname=grantee)\n    and machine!='yourlocalhostname' \norder by LAST_CALL_ET desc;\n</code></pre>\n"}, {'answer_id': 7806123, 'author': 'Thomas Bratt', 'author_id': 15985, 'author_profile': 'https://Stackoverflow.com/users/15985', 'pm_score': 3, 'selected': False, 'text': "<p>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:</p>\n\n<pre><code>BEGIN\n  FOR c IN (\n      SELECT s.sid, s.serial#\n      FROM v$session s\n      WHERE (s.Osuser = 'MyUser' or s.MACHINE = 'MyNtDomain\\MyMachineName')\n      AND s.USERNAME &lt;&gt; 'SYS'\n      AND s.STATUS &lt;&gt; 'KILLED'\n  )\n  LOOP\n      EXECUTE IMMEDIATE 'alter system kill session ''' || c.sid || ',' || c.serial# || '''';\n  END LOOP;\nEND;\n</code></pre>\n"}, {'answer_id': 9117640, 'author': 'Vadzim', 'author_id': 603516, 'author_profile': 'https://Stackoverflow.com/users/603516', 'pm_score': 2, 'selected': False, 'text': '<p>Additional info</p>\n\n<blockquote>\n  <p>Important Oracle 11g changes to alter session kill session</p>\n  \n  <p>Oracle author Mladen Gogala notes that an @ sign is now required to\n  kill a session when using the inst_id column:</p>\n</blockquote>\n\n<pre><code>alter system kill session \'130,620,@1\';\n</code></pre>\n\n<p><a href="http://www.dba-oracle.com/tips_killing_oracle_sessions.htm" rel="nofollow">http://www.dba-oracle.com/tips_killing_oracle_sessions.htm</a></p>\n'}, {'answer_id': 36299914, 'author': 'Ramki', 'author_id': 4136306, 'author_profile': 'https://Stackoverflow.com/users/4136306', 'pm_score': 0, 'selected': False, 'text': "<p>If Oracle is running in Unix /Linux then we can grep for all client connections and kill it </p>\n\n<p>grep all oracle client process:</p>\n\n<p>ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}' | wc -l</p>\n\n<p>Kill all oracle client process :</p>\n\n<p>kill -9 <code>ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}'</code></p>\n"}]
['https://Stackoverflow.com/questions/55342', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/685/']