Re: Extracting SQL_TEXT from v$SQLAREA

The pointers to the previous statement are maintained in v$session (IIRC sql_prev_address and sql_prev_hash_value). However, maybe you should consider monitor the sqlarea directly, in conjunction with the v$access table, which will show you which sessions have the table open.
You could of course also trace individual sessions.

Hth,

Sybrand Bakker, Oracle DBA

"NEO" <sneo_at_my-deja.com> wrote in message news:91tsp9$rfp$1_at_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 - 16:09:52 CST