Get email alerts, when someone accesses specific tables

I wanted to create a procedure, which will be run by a job every few seconds, and get email alerts every time someone accesses specific tables.
I want to know usernames, terminal names, some additional session info and the SQL statement executed.
I used UTL_MAIL package, which is not installed by default, so I had it installed.
Let's say I want to monitor a table, called AR_PAYMENT_SCHEDULES_ALL.
The procedure is:

CREATE OR REPLACE PROCEDURE SYSTEM.CHECKSQL
IS
   TYPE T_CURSOR IS REF CURSOR;

   V_CURSOR      T_CURSOR;
   V_INST        NUMBER;
   V_SID         NUMBER;
   V_SERIAL      NUMBER;
   V_USERNAME    VARCHAR2 (30);
   V_OSUSER      VARCHAR2 (30);
   V_MACHINE     VARCHAR2 (64);
   V_TERMINAL    VARCHAR2 (30);
   V_PROGRAM     VARCHAR2 (48);
   V_MODULE      VARCHAR2 (48);
   V_LOGONTIME   DATE;
   V_SQLTEXT     VARCHAR2 (1000);
   V_OUTPUT      VARCHAR2 (32767);
   V_ALTER       VARCHAR2 (500);
BEGIN
   V_OUTPUT := NULL;

   OPEN V_CURSOR FOR
      SELECT B.INST_ID,
             B.SID,
             B.SERIAL#,
             B.USERNAME,
             B.OSUSER,
             B.MACHINE,
             B.TERMINAL,
             B.PROGRAM,
             B.MODULE,
             B.LOGON_TIME,
             A.SQL_TEXT
        FROM GV$SQL A, GV$SESSION B
       WHERE     A.SQL_ID = B.SQL_ID
             AND A.INST_ID = B.INST_ID
             AND UPPER (A.SQL_TEXT) LIKE '%AR_PAYMENT_SCHEDULES_ALL%'
             AND UPPER (A.SQL_TEXT) NOT LIKE '%V$SESSION%';

   LOOP
      FETCH V_CURSOR
      INTO V_INST,
           V_SID,
           V_SERIAL,
           V_USERNAME,
           V_OSUSER,
           V_MACHINE,
           V_TERMINAL,
           V_PROGRAM,
           V_MODULE,
           V_LOGONTIME,
           V_SQLTEXT;

      EXIT WHEN V_CURSOR%NOTFOUND;
      V_OUTPUT :=
            V_OUTPUT
         || CHR (13)
         || CHR (10)
         || 'INSTANCE: '
         || V_INST
         || CHR (13)
         || CHR (10)
         || 'SID: '
         || V_SID
         || CHR (13)
         || CHR (10)
         || 'SERIAL#: '
         || V_SERIAL
         || CHR (13)
         || CHR (10)
         || 'USERNAME: '
         || V_USERNAME
         || CHR (13)
         || CHR (10)
         || 'OSUSER: '
         || V_OSUSER
         || CHR (13)
         || CHR (10)
         || 'MACHINE: '
         || V_MACHINE
         || CHR (13)
         || CHR (10)
         || 'TERMINAL: '
         || V_TERMINAL
         || CHR (13)
         || CHR (10)
         || 'PROGRAM: '
         || V_PROGRAM
         || CHR (13)
         || CHR (10)
         || 'MODULE: '
         || V_MODULE
         || CHR (13)
         || CHR (10)
         || 'LOGON TIME: '
         || TO_CHAR (V_LOGONTIME, 'DD/MM/RR HH:MI:SS')
         || CHR (13)
         || CHR (10)
         || 'SQL: '
         || V_SQLTEXT
         || CHR (13)
         || CHR (10)
         || '**************************************************';
   END LOOP;

   CLOSE V_CURSOR;

   IF V_OUTPUT IS NOT NULL
   THEN
      V_ALTER := 'ALTER SESSION SET SMTP_OUT_SERVER = ''[YourSMTPserver.YourDomain.com]''';

      EXECUTE IMMEDIATE V_ALTER;

      UTL_MAIL.SEND_ATTACH_VARCHAR2 (
         SENDER         => '[The sender's email address, can be fake]',
         RECIPIENTS     => '[The email addresses of the recipients, separated by commas]',
         SUBJECT        => 'SQL CAUGHT',
         ATT_FILENAME   => 'sessions.txt',
         ATTACHMENT     => V_OUTPUT);
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END CHECKSQL;
/

Whenever a user accesses this table, I get an email with subject 'SQL CAUGHT' and an attached text file, named 'sessions.txt'.
A sample of this file is:

INSTANCE: 2
SID: 8125
SERIAL#: 7646
USERNAME: APPS
OSUSER: oracle
MACHINE: ecapps2
TERMINAL:
PROGRAM:
MODULE: XXIEX_GRP_DATA_PARALLEL
LOGON TIME: 01/12/11 12:15:18
SQL: SELECT /*+ use_nl(ps x) index_ffs(x XXIEX_SCORING_INVOICES_U1) */ :B1 ,X.PARTY_ID ,PS.CUSTOMER_TRX_ID ,PS.TRX_DATE ,PS.TRX_NUMBER ,ROUND(PS.AMOUNT_DUE_ORIGINAL * NVL(PS.EXCHANGE_RATE, 1), 2) AMOUNT_DUE_ORIGINAL ,PS.ACCTD_AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING ,PS.DUE_DATE ,PS.ORG_ID ,XXIEX_IEX_CUSTOMER_SCORING_PKG.FUN_GET_APPL_AMOUNT(PS.CUSTOMER_TRX_ID, PS.DUE_DATE, :B2 ) FROM AR_PAYMENT_SCHEDULES_ALL PS ,XXIEX_SCORING_INVOICES X WHERE X.REQUEST_ID = :B1 AND PS.CUSTOMER_TRX_ID = X.CUSTOMER_TRX_ID AND PS.CLASS IN ('INV', 'DM', 'CM')
**************************************************
INSTANCE: 1
SID: 8111
SERIAL#: 53187
USERNAME: AFRAGOU
OSUSER: afragou
MACHINE: CENTRAL-DOMAIN\OTE-8B2-015
TERMINAL: OTE-8B2-015
PROGRAM: oracle@ecdb1 (P005)
MODULE: TOAD 9.7.2.5
LOGON TIME: 01/12/11 12:33:30
SQL: CREATE TABLE  AR_PS_WRONG_DIAKAN_PART012 AS (SELECT /*+  PARALLEL(A,6)    */        *   FROM apps.ar_payment_schedules_all partition(AR_PAYMENT_SCHEDULES_PART012)a WHERE        a.CLASS = 'DM'    AND a.status = 'OP'    AND EXISTS (           SELECT   b.customer_id, COUNT(*),b.trx_date,                    b.amount_due_original               FROM apps.ar_payment_schedules_all b              WHERE b.CLASS = 'DM'                AND b.customer_id = a.customer_id                AND b.trx_date = a.trx_date                AND b.amount_due_original = a.amount_due_original           GROUP BY b.customer_id, b.trx_date, b.amount_due_original             HAVING COUNT (b.customer_id) > 1                AND COUNT (TRUNC (b.trx_date)) > 1                AND COUNT (b.amount_due_original) > 1)                      )
**************************************************

10g & 11g: In new patchsets auditing by session is "obsolete"

We create a table:

CREATE TABLE HELPDESK.AUD_TEST(TEST NUMBER);

We start auditing user helpdesk "insert table" statements by session:

AUDIT INSERT TABLE BY HELPDESK BY SESSION;

We verify it:

SELECT *
  FROM DBA_STMT_AUDIT_OPTS A
 WHERE A.USER_NAME = 'HELPDESK';

  

USER_NAME	PROXY_NAME	AUDIT_OPTION	SUCCESS	FAILURE
HELPDESK		INSERT TABLE	BY SESSION	BY SESSION

We open a sqlplus, connect as helpdesk and insert 6 rows in aud_test table:

SQL> CONN HELPDESK@ECDB1
Enter password: ********
Connected.

SQL> INSERT INTO AUD_TEST VALUES (1);
1 row created.

SQL> COMMIT;
Commit complete.

SQL> INSERT INTO AUD_TEST VALUES (1);
1 row created.

SQL> INSERT INTO AUD_TEST VALUES (1);
1 row created.

SQL> INSERT INTO AUD_TEST VALUES (1);
1 row created.

SQL> INSERT INTO AUD_TEST VALUES (1);
1 row created.

SQL> INSERT INTO AUD_TEST VALUES (1);
1 row created.

SQL> COMMIT;
Commit complete.

We query aud$:

SELECT SESSIONID,
       USERID,
       ACTION#,
       OBJ$CREATOR,
       OBJ$NAME,
       SES$ACTIONS,
       NTIMESTAMP#
  FROM SYS.AUD$
 WHERE USERID = 'HELPDESK';

SESSIONID	USERID	ACTION#	OBJ$CREATOR	OBJ$NAME	SES$ACTIONS	NTIMESTAMP#
290823972	HELPDESK	103	HELPDESK	AUD_TEST	------S---------	11/2/2011 08:48:17
290823972	HELPDESK	103	HELPDESK	AUD_TEST	------S---------	11/2/2011 08:48:19
290823972	HELPDESK	103	HELPDESK	AUD_TEST	------S---------	11/2/2011 08:48:20
290823972	HELPDESK	103	HELPDESK	AUD_TEST	------S---------	11/2/2011 08:48:21
290823972	HELPDESK	103	HELPDESK	AUD_TEST	------S---------	11/2/2011 08:48:21
290823972	HELPDESK	103	HELPDESK	AUD_TEST	------S---------	11/2/2011 08:47:49

You would expect to see only one row, for the 6 insert statements executed by one session in the same table.
But, instead, we get 6 rows in the AUD$ table, as if we had chosen the "by access" auditing method.
According to Oracle Note 6023472.8:

    Bug 6023472 Collection of changes to auditing

    This issue is fixed in:
    10.2.0.5 (Server Patch Set)
    11.1.0.7 (Server Patch Set)
    11.2.0.1 (Base Release)

    This fix covers several changes to audit behaviour:

    1. The LOGON (action# = 100) audit records are no longer updated. So, when a session ends and if sessions are audited, a LOGOFF (action# = 101) audit record will be written as a separate audit record.
    2. Auditing configured as 'By Session' will no longer produce one audit record per session. Each occurrence of the audit event will result in newer audit records since no audit record is updated.
    3. The index SYS.I_AUD1 on the AUD$ table is dropped since there are no longer any updates to AUD$ with this fix.
    4. FGA_LOG$ will not have PLHOL column.
    5. OS/XML Audit filenames will have a filename format of the form:
    ___.
    6. The default value for Max Audit FileSize is 1000KB and Max Audit FileAge is 1 Day. This means that once one of these max limits are reached, the audit records will be written in a new audit file with same "___" part, but with a new "".

According to (2), auditing by session is, in essence, no longer supported for these patchsets and onwards.