Re: Auditing an app's SQL - How?

Walt wrote:
> This is probably a basic question but I can't find the answer in any of
> the available docementation.
>
> We've got an app that's somewhat of a black box. How can we determine
> at the database end what SQL it's running?

Look in V$SQL_PLAN, or V$SQL_TEXT or V$SQL_AREA. (Though I can never quite remember where or whether the underscores go in some of those names, so be prepared to experiment).

> In particular, I'm
> interested in the INSERT statements it is making against a particular
> table.
>
> The dba_audit_trail doesn't capture the SQL and fine grained auditing
> only appears to support SELECT statements in 9.2.

Of course, every normal insert will generate Redo. So make friends with Log Miner, and you'll be able to see the redo your insert statements are generating.

> Can anybody point me to a tool/command that effectively says "spool all
> the INSERT statements on table my_table to a place where I can review
> them?" i.e. the equivalent of SQL Profiler in SQL Server?

It is not all relevant, but read this:

http://www.dizwell.com/html/pitr_alternatives.html

...and specifically look at the bit that describes how to work with Log Miner (there is also a GUI viewer in Enterprise Manager).

Regards
HJR
>
> Thanks.
>
> Oracle 9.2.0.4, W2k3.
>
Received on Tue Dec 07 2004 - 14:53:30 CST