DROP TABLE dbms_job_errors CASCADE CONSTRAINTS;

CREATE TABLE dbms_job_errors (job_number INTEGER NOT NULL
                            , failure_date DATE DEFAULT SYSDATE NOT NULL
                            , username VARCHAR2(30) DEFAULT USER
                            , error_message VARCHAR2(4000)
                            , CONSTRAINT dbms_job_errors_pk PRIMARY KEY (job_number, failure_date)
                             )
TABLESPACE users
LOGGING
MONITORING;

CREATE OR REPLACE PUBLIC SYNONYM dbms_job_errors FOR dbms_job_errors;

GRANT SELECT ON dbms_job_errors TO PUBLIC;

/* --------------------------------------------------------------------------- */

CREATE OR REPLACE PROCEDURE SYS.rac_kill_session
(
   p_instance_number   IN   gv$instance.instance_number%TYPE
 , p_sid               IN   v$session.SID%TYPE
 , p_serial            IN   v$session.serial#%TYPE
)
AUTHID DEFINER AS
-- Variables
   l_session_exists                   PLS_INTEGER;
   l_instance_name                    gv$instance.instance_name%TYPE;
   l_this_instance_number             v$instance.instance_number%TYPE;
   l_this_instance_name               v$instance.instance_name%TYPE;
   l_job                              dba_jobs.job%TYPE;
-- Custom Exceptions
   session_does_not_exist             EXCEPTION;
   instance_does_not_exist            EXCEPTION;
BEGIN
   -- First see if the session really exists...
   BEGIN
      SELECT 1
        INTO l_session_exists
        FROM gv$session
       WHERE inst_id = p_instance_number
         AND SID = p_sid
         AND serial# = p_serial;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         RAISE session_does_not_exist;
   END;

   SELECT instance_number
        , instance_name
     INTO l_this_instance_number
        , l_this_instance_name
     FROM v$instance;

   /* If they are trying to kill a session on this instance
      just issue the ALTER SYSTEM KILL SESSION command... */
   IF p_instance_number = l_this_instance_number THEN
      EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || p_sid || ',' || p_serial || '''';

      DBMS_OUTPUT.put_line ('Successfully Killed the session with SID: ' || p_sid || ', Serial#: ' || p_serial || ' on RAC Instance #: ' || p_instance_number || ' (RAC Node: ' || l_this_instance_name || ').');
   ELSE
      /* We must use DBMS_JOB with the instance parameter in order to kill this session
         since it is on a different RAC node... */

      -- Get the instance node name
      BEGIN
         SELECT instance_name
           INTO l_instance_name
           FROM gv$instance
          WHERE instance_number = p_instance_number;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            RAISE instance_does_not_exist;
      END;

      DBMS_JOB.submit (job           => l_job
                     , what          =>    'DECLARE l_error VARCHAR2(4000); '
                                        || 'BEGIN '
                                        || '   EXECUTE IMMEDIATE ''ALTER SYSTEM KILL SESSION '''''
                                        || p_sid
                                        || ','
                                        || p_serial
                                        || '''''''; '
                                        || 'EXCEPTION '
                                        || '   WHEN OTHERS THEN '
                                        || '      l_error := SQLERRM; '
                                        || '      INSERT INTO dbms_job_errors (job_number, failure_date, username, error_message) '
                                        || '      VALUES (sys_context(''USERENV'', ''BG_JOB_ID''), SYSDATE, USER, l_error); '
                                        || '      COMMIT; '
                                        || 'END;'
                     , INSTANCE      => p_instance_number
                     , FORCE         => FALSE
                      );
      -- Commit to launch the job
      COMMIT;
      DBMS_OUTPUT.put_line ('Launched DBMS_JOB #: ' || l_job || ' to kill the session with SID: ' || p_sid || ', Serial#: ' || p_serial || ' on RAC Instance #: ' || p_instance_number || ' (RAC Node: ' || l_instance_name || ').');
   END IF;
EXCEPTION
   WHEN session_does_not_exist THEN
      raise_application_error (-20101, 'A session with SID: ' || p_sid || ', Serial #: ' || p_serial || ', on Instance #: ' || p_instance_number || ' does not exist in GV$SESSION.');
   WHEN instance_does_not_exist THEN
      raise_application_error (-20102, 'A RAC Node with INSTANCE_NUMBER: ' || p_instance_number || ' does not exist in GV$INSTANCE.');
END rac_kill_session;
/