Extracting SQL_TEXT from v$SQLAREA

From: NEO <sneo_at_my-deja.com>
Date: Thu, 21 Dec 2000 21:30:16 GMT
Message-ID: <91tsp9$rfp$1@nnrp1.deja.com>

I am auditing SELECT statements on a particular table (say MyTable). I need to extract the SQL statement issued during the SELECT. Since, the AUD$ table does not store the SQL statement, I tried the following apporach:

1. Change AUD$ ownership to SYSTEM and grant SYS all privileges for the table.
2. Attach a AFTER INSERT trigger to the AUD$ table to extract the SQL statement issued by the user.

CREATE OR REPLACE ...
AFTER INSERT ON SYSTEM.AUD$
...
BEGIN
 INSERT INTO SOMETABLE
  SELECT B.SQL_TEXT
  FROM V$SESSION A, V$SQLAREA B

  WHERE  (A.SQL_HASH_VALUE = B.HASH_VALUE)
     AND (A.SQL_ADDRESS = B.ADDRESS)
     AND ( A.AUDSID = :NEW.SESSIONID);

END; The trigger works but does not grab the SELECT statement issued by the user. Instead, it grabs the INSERT statement that I am issuing in the trigger ("INSERT INTO SOMETABLE ... ") from v$SQLAREA.

When I issue "SELECT B.SQL_TEXT FROM V$SESSIOn A, ... AND (A.AUDSID = ###)" directly in SQL*Plus, I get the correct SELECT statement I am looking for.

I assume my trigger is grabing the currently executing SQL statement. Is there a way for me to grab the SQL statement that caused the insert into the AUD$ table such as: "Select count(*) from MyTable" ?

Thanks.

Sent via Deja.com
http://www.deja.com/ Received on Thu Dec 21 2000 - 15:30:16 CST