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