Re: Capturing SQL

In article <9095l6$tfs$1_at_nnrp1.deja.com>,   Joe Maloney <jrpm_at_my-deja.com> wrote:
> You did not say what version of Oracle you are using, but

We are running 7.3.4

> 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.

Yes, it probably has way more that I need, but the performance hit... Plus, we have a 24x7 operation and IIRC turning auditing on/off requires taking the database off line.

> 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;
>

Thanks, I'm already doing that, I just wanted the trigger to capture the SQL as well.

Andrew A.

> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Dec 04 2000 - 07:30:16 CST