-- Allows you to audit session duration, program used, 
-- last action, etc. Notice how I exclude the 
-- sessions from host 'hurray'  because there would be too many logins!
-- Much of this code was taken from Burleson's website here:
-- www.dba-oracle.com/art_dbazine_sys_trigs.htm
-- I had to rewrite it because I got errors on our 9i database (Open VMS).

CREATE OR REPLACE PACKAGE session_audit
AS
/******************************************************************************
   NAME:       session_audit
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        8/16/2004   Edward Stoever   1. Created this package.

   Requires the following table:

      CREATE TABLE SESSION_LOG
      (
        USER_ID          VARCHAR2(30 BYTE),
        SESSION_ID       NUMBER(8),
        HOST             VARCHAR2(30 BYTE),
        LAST_PROGRAM     VARCHAR2(48 BYTE),
        LAST_ACTION      VARCHAR2(32 BYTE),
        LAST_MODULE      VARCHAR2(32 BYTE),
        LOGON_DAY        DATE,
        LOGON_TIME       VARCHAR2(10 BYTE),
        LOGOFF_DAY       DATE,
        LOGOFF_TIME      VARCHAR2(10 BYTE),
        ELAPSED_MINUTES  NUMBER(8)
      )
      TABLESPACE DEVELOPMENT;
      <<<>>>FORWARD SLASH HERE<<<>>>

--	Requires the following triggers:

       CREATE OR REPLACE TRIGGER logconnects
         AFTER LOGON ON DATABASE
      DECLARE
      BEGIN
         IF SYS_CONTEXT ('USERENV', 'HOST') <> 'hurray'
         THEN
            session_audit.log_connect (USER,
                                       SYS_CONTEXT ('USERENV', 'SESSIONID'),
                                       SYS_CONTEXT ('USERENV', 'HOST')
                                      );
         END IF;
      END;
      <<<>>>FORWARD SLASH HERE<<<>>>

       CREATE OR REPLACE TRIGGER logdisconnects
         BEFORE LOGOFF ON DATABASE
       DECLARE
         var_last_program   session_log.last_program%TYPE;
         var_last_module    session_log.last_module%TYPE;
         var_last_action    session_log.last_action%TYPE;
       BEGIN
         IF SYS_CONTEXT ('USERENV', 'HOST') <> 'hurray'
         THEN
            SELECT program, module, action
              INTO var_last_program, var_last_module, var_last_action
              FROM v$session
             WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid;

            session_audit.log_disconnect (SYS_CONTEXT ('USERENV', 'SESSIONID'),
                                          var_last_program,
                                          var_last_action,
                                          var_last_module
                                         );
         END IF;
      END;
      <<<>>>FORWARD SLASH HERE<<<>>>
******************************************************************************/
   PROCEDURE log_connect (
      user_id_      VARCHAR2,
      session_id_   NUMBER,
      host_         VARCHAR2
   );

   PROCEDURE log_disconnect (
      session_id_     NUMBER,
      last_program_   VARCHAR2,
      last_action_    VARCHAR2,
      last_module_    VARCHAR2
   );
END session_audit;
/


CREATE OR REPLACE PACKAGE BODY session_audit
AS
/******************************************************************************
   NAME:       session_audit
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        8/16/2004   Edward Stoever   1. Created this package body.

******************************************************************************/
   PROCEDURE log_connect (
      user_id_      VARCHAR2,
      session_id_   NUMBER,
      host_         VARCHAR2
   )
   IS
   BEGIN
      INSERT INTO session_log
           VALUES (user_id_, session_id_, host_, NULL, NULL, NULL, SYSDATE,
                   TO_CHAR (SYSDATE, 'hh24:mi:ss'), NULL, NULL, NULL);

     -- COMMIT;

      DELETE FROM session_log
            WHERE logoff_day < SYSDATE - 30;
   END;

   PROCEDURE log_disconnect (
      session_id_     NUMBER,
      last_program_   VARCHAR2,
      last_action_    VARCHAR2,
      last_module_    VARCHAR2
   )
   IS
   BEGIN
      UPDATE session_log
         SET last_action = last_action_
       WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;

      UPDATE session_log
         SET last_program = last_program_
       WHERE session_id_ = session_id;

      UPDATE session_log
         SET last_module = last_module_
       WHERE session_id_ = session_id;

      UPDATE session_log
         SET logoff_day = SYSDATE
       WHERE session_id_ = session_id;

      UPDATE session_log
         SET logoff_time = TO_CHAR (SYSDATE, 'hh24:mi:ss')
       WHERE session_id_ = session_id;

      UPDATE session_log
         SET elapsed_minutes = ROUND ((logoff_day - logon_day) * 1440)
       WHERE session_id_ = session_id;

   --   COMMIT;
   END;
END session_audit;
/