Re: Capturing SQL

You did not say what version of Oracle you are using, but

1-Have you checked out Oracle's internal auditing? It has an audit subsystem that may do what you need. That would be easier than writing your own subsystem.

2-If you are using 8.1.x, then consider on update-etc triggers on the table, using the :New and :old values, username(), etc.

     if :new.column <> :old.column then
          insert into holding (user,oldval,newval)
          values (username(),:old.column,:new.column);
     end if;

I mention 8.1.x because there were occasionaly mutating table issues (at least I had them) in earlier releases.

In article <9093do$rku$1_at_nnrp1.deja.com>,   argon <a_____n_at_my-deja.com> wrote:
> Hello,
>
> I need to create auditing of sorts: track certain changes
> to a certain column - who /what is changing it. I also
> need to capture the SQL update statement that did it.
>
> So I created a holding table and a trigger that would
> populate the holding table on update to the column in
> question. I've tried to capture the SQL using:
>
> select t2.sql_text
> from sys.v_$session t1, sys.v_$sqltext t2
> where t1.audsid = userenv('sessionid')
> and t2.address = t1.prev_sql_addr
> and t2.hash_value = t1.prev_hash_value
> order by t2.piece
>
> It does not work - always brings the generic
> 'update ts$ set inc#=:2 where ts#=:1'
>
> Any suggestions?
>
> Andrew A.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

--
Joseph R.P. Maloney, CCP,CSP,CDP
MPiR, Inc.
502-451-7404
some witty phrase goes here, I think.


Sent via Deja.com http://www.deja.com/
Before you buy.