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)                      )
**************************************************

OEM: "Tablespace Used (%)" alert is not triggered

There was an incident, where a tablespace filled completely, but I did not get any alert from OEM.

There is Oracle Note 403264.1:"Troubleshooting a Database Tablespace Used(%) Alert problem", where several issues about this alert are discussed.
In my case, there was no entry in DBA_OUTSTANDING_ALERTS,
although in DBA_TABLESPACE_USAGE_METRICS the particular tablespace had exceeded the thresholds I have set.

So, according to Section 4 in this note, this is probably caused by database bugs 7462407, 5245039.
Additionally, I have a "gap" in my v$tablespace.

Unfortunately, there are no patches for these bugs for Windows platforms, so I created a user defined metric to get these alerts.
The new metric was based on DBA_TABLESPACE_USAGE_METRICS, which is very nice, because it queries v$filespace_usage, which keeps track of any MAXSIZE value you may have set in your autoextended datafiles.
If it is set to UNLIMITED, then this value is equal to 32 GB for 8 KB block size (2^22-1 * 8 KB).

Keep in mind, columns referencing sizes in these 2 views are blocks.
So, if your tablespace has block size of 8 KB, multiply these values by 8 to get its size in KB.

Of course, there is no way for your database to know, if your filesystems will fill, e.g. if in your filesystem you have 10 GB of free space and an autoextended datafile in it with MAXSIZE 15 GB, you will not get any alert, even from the default OEM metric, when your filesystem's free space depletes.
You should use "Filesystem Space Available (%)" alert at host level, to check your filesystem's free space.

Direct Path INSERT requires exclucive lock

There is a table, where we insert rows using serial direct-path INSERT [INSERT /*+ APPEND */].
The INSERT command is executed periodically by a job.
Manually, I run a DELETE command on this table to purge rows no longer needed.
At some point, the job mentioned executes the direct-path INSERT and it gets blocked by the DELETE command with an "enq: TM - contention" lock.

INST_ID	SID	ID1	ID2	LMODE	BLOCK	REQUEST	OBJECT_NAME	LOCK_TYPE	PROGRAM
2	8016	760488	0	3	1	0	AUD$_BU_IDX2	DML	sqlplus@ecdb2 (TNS V1-V3)
2	--->8296	760488	0	0	0	6		DML	oracle@ecdb2 (J001)

According to Oracle Database Administrator's Guide 10g Release 2 (10.2), this is normal behavior:

    During direct-path INSERT, the database obtains exclusive locks on the table (or on all partitions of a partitioned table). As a result, users cannot perform any concurrent insert, update, or delete operations on the table, and concurrent index creation and build operations are not permitted. Concurrent queries, however, are supported, but the query will return only the information before the insert operation.