{"id":192,"date":"2017-08-17T04:42:14","date_gmt":"2017-08-17T04:42:14","guid":{"rendered":"http:\/\/www.mrmarkyoung.com\/oracle\/?p=192"},"modified":"2018-06-04T16:46:13","modified_gmt":"2018-06-04T06:46:13","slug":"kill-user-sessions-in-rac-10g","status":"publish","type":"post","link":"http:\/\/www.mrmarkyoung.com\/oracle\/2017\/08\/17\/kill-user-sessions-in-rac-10g\/","title":{"rendered":"Kill user sessions in RAC 10g"},"content":{"rendered":"<p><strong>Goal<\/strong><\/p>\n<p>The 10g version of Oracle RAC only allows ALTER SYSTEM KILL SESSION (SID,SERIAL#); as a local session on a local node.<\/p>\n<p>To kill from one node sessions across multiple nodes the following script has been created&#8230;<\/p>\n<pre class=\"lang:default decode:true \">Kill_Session_User(p_username VARCHAR2)<\/pre>\n<p>\u2013 This takes the schema\/user name and will loop through all sessions for this user in gv$sessions<br \/>\n&#8212; Then uses supplies this SID, SERIAL#, INST_ID to Kill_Session_Job<\/p>\n<p>Kill_Session_Job(p_sid NUMBER, p_serial NUMBER, p_inst_id NUMBER)<br \/>\n&#8212; This takes the session SID and SERIAL# along with the INSTANCE_ID number and schedules the local Kill_Session jobs on the RAC nodes<\/p>\n<p>Kill_Session(p_sid NUMBER, p_serial NUMBER)<br \/>\n&#8212; This just does a simple ALTER SYSTEM KILL SESSION (sid,serial#)<\/p>\n<p><strong>Solution<\/strong><\/p>\n<p>NOTE: This can be run as SYS or SCOTT<\/p>\n<pre class=\"lang:default decode:true\">SCOTT requires the following grants\r\ngrant alter system to SCOTT;\r\ngrant create job to SCOTT;<\/pre>\n<p>To Kill All Sessions For A UserName<\/p>\n<pre class=\"lang:default decode:true \">set serveroutput on size 1000000\r\nexec GISDBA.DBA_10g_RAC.Kill_Session_User(p_username =&gt; 'SCOTT');\r\n\r\nExpected feedback\r\n<\/pre>\n<p>++ USER = &#8220;SCOTT&#8221; SID = &#8220;470&#8221; SERAL = &#8220;35488&#8221; INSTANCE = &#8220;2&#8221; +++ begin GISDBA.DBA_10g_RAC.Kill_Session(p_sid =&gt; 470, p_serial =&gt; 35488); end;<br \/>\n+++ USER = &#8220;SCOTT&#8221; SID = &#8220;507&#8221; SERAL = &#8220;40797&#8221; INSTANCE = &#8220;1&#8221; +++ begin GISDBA.DBA_10g_RAC.Kill_Session(p_sid =&gt; 507, p_serial =&gt; 40797); end;<\/p>\n<p>===== All the database sessions of user &#8220;SCOTT&#8221; killed successfully ! ===== PL\/SQL procedure successfully completed. To Kill A Session on any node<\/p>\n<pre class=\"lang:default decode:true \">define user='SCOTT'\r\nselect username,SID, SERIAL#,INST_ID,program from gv$session where username='&amp;user';\r\n \r\nUSERNAME                              SID    SERIAL#    INST_ID PROGRAM\r\n------------------------------ ---------- ---------- ---------- ------------------------------------------------\r\nSCOTT                                 507      40797          1 sqlplus.exe\r\nSCOTT                                 470      35488          2 sqlplus.exe\r\n \r\nset serveroutput on size 1000000\r\nexec GISDBA.DBA_10g_RAC.Kill_Session_Job(p_sid =&gt; 470, p_serial =&gt; 35488, p_inst_id =&gt; 2);<\/pre>\n<pre class=\"lang:default decode:true\">Expected feedback\r\n\r\nbegin DBA_10g_RAC.Kill_Session(p_sid =&gt; 470, p_serial =&gt; 35488); end;\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Goal The 10g version of Oracle RAC only allows ALTER SYSTEM KILL SESSION (SID,SERIAL#); as a local session on a local node. To kill from one node sessions across multiple nodes the following script has been created&#8230; Kill_Session_User(p_username VARCHAR2) \u2013 This takes the schema\/user name and will loop through all sessions for this user in &#8230; <a title=\"Kill user sessions in RAC 10g\" class=\"read-more\" href=\"http:\/\/www.mrmarkyoung.com\/oracle\/2017\/08\/17\/kill-user-sessions-in-rac-10g\/\" aria-label=\"Read more about Kill user sessions in RAC 10g\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[53,43],"class_list":["post-192","post","type-post","status-publish","format-standard","hentry","category-oracle-database","tag-kill","tag-rac"],"_links":{"self":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/192","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/comments?post=192"}],"version-history":[{"count":4,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/192\/revisions"}],"predecessor-version":[{"id":196,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/192\/revisions\/196"}],"wp:attachment":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/media?parent=192"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/categories?post=192"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/tags?post=192"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}