title: Stored procedure to kill Oracle sessions by user name
body: <p>This has been asked several times on the web, but none of the answers I found on Google could solve my problem.</p>
  
  <p>I'd like to create a stored procedure that kills Oracle sessions. The only parameter the procedure accepts is the user name of the owner of the sessions to kill.</p>
  
  <p>This is my attempt:</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE kill_user_session (
      username   IN NVARCHAR2
  )
  AS
      stmt varchar(5000);
      CURSOR get_sessions
          IS
          SELECT s.sid sid, s.serial# ser
              FROM v$session s, v$process p
          WHERE s.username = username
          AND p.addr(+) = s.paddr;
      session_rec get_sessions%ROWTYPE;
  BEGIN
      FOR session_rec in get_sessions LOOP
          BEGIN
              stmt := 'ALTER SYSTEM KILL SESSION ''' || session_rec.sid || ',' ||  session_rec.ser || '''';
              EXECUTE IMMEDIATE stmt;
          --EXCEPTION WHEN others THEN
          --    dbms_output.put_line('Error killing session: ' || stmt);
          --    dbms_output.put_line(SQLERRM);
          END;
      END LOOP;
  END;
  /
  </code></pre>
  
  <p>If I execute it like this</p>
  
  <pre><code>     exec kill_user_session('myuser');
  </code></pre>
  
  <p>I get an error:</p>
  
  <pre><code>ERROR at line 1:
  ORA-00911: invalid character
  ORA-06512: at "SYSTEM.KILL_USER_SESSION", line 17
  ORA-06512: at line 1
  </code></pre>
  
  <p>If I change line 17 to </p>
  
  <pre><code>            stmt := 'ALTER SYSTEM KILL SESSION "' || session_rec.sid || ',' ||  session_rec.ser || '"';
  </code></pre>
  
  <p>then I get</p>
  
  <pre><code>ERROR at line 1:
  ORA-00026: missing or invalid session ID
  ORA-06512: at "SYSTEM.KILL_USER_SESSION", line 17
  ORA-06512: at line 1
  </code></pre>
  
  <p>I have granted the following rights to SYSTEM:</p>
  
  <pre><code>GRANT SELECT ON v$session TO SYSTEM;
  GRANT ALTER SYSTEM TO SYSTEM;
  </code></pre>
  
  <p>But that didn't help.</p>
  
  <p><strong>EDIT:</strong> I added a <code>dbms_output.putline</code> to print out the <code>stmt</code> variable before executing it. Here's an example:</p>
  
  <pre><code>ALTER SYSTEM KILL SESSION "34,91"
  </code></pre>
  
  <p>If I execute this statement outside of the stored procedure, it runs fine and the session is killed. But not from inside.</p>