Re: How to capture SQL

In article <Xns9548BB2C8447BSunnySD_at_68.6.19.6>, anacedent_at_hotmail.com says...
> "Randy Harris" <randy_at_SpamFree.com> wrote in
> news:TrxUc.2712$ZC7.249_at_newssvr19.news.prodigy.com:
>
> > Is there a simple means of capturing an SQL statement sent from an
> > application to an Oracle 9i server (on the server end)?
>
> No, there is not.
> I am not saying it is impossible, just not simple.
> It depends on a number of factors none of which you specified.
>

Hmmmm !

If you have access to the source of the application - then you have the SQL already.

If not, and you can have one session (ONLY) connected to the database then you can log into sqlplus as system and :

ALTER SYSTEM SET SQL_TRACE = TRUE; Get the app to do its thing then

ALTER SYSTEM SET SQL_TRACE = FALSE; There will be a trace file in wherever your user_dump_dest parameter points to. Find it, and tkprof if (or just read the raw trace file - if you like a bit of fun !)

If you can't have the one session only, use DBMS_SUPPORT.SET_TRACE_IN_SESSION to set the equivalent of a 10046 trace for whichever session you identify as being the application. You might need to run ?/rdbms/admin/dbmssupp.sql as sysdba to install the dbms_support package. Check metalink for details on using it.

You'll get another tracefile with that option.

Have fun !

Cheers,
Norm.

-- 

Delete the obvious bit (and the dot) to reply by email.