Re: Activate SQL logging

"MCArch" <spamfree_at_thankyou.net> wrote in message news:gcBPd.6855$hU7.6706_at_newssvr33.news.prodigy.com...
> I guess I deserve that answer for not indicating that the server is Oracle
> 8.1.7.4.

I don't think you deserved that answer at all, had the response been version specific I'd expect that you would have been called on it. Suggesting DB2 for a question *only* posed in an Oracle forum is either unhelpful or trolling in my view. I'm certainly sure that if I suggested transportable tablespaces as a data transfer technique in an ibm forum I would rightly be picked up on it.

Meanwhile I'm not sure that Mark's suggestion of logminer is necessarily quite correct. I think what you are after is SQL_TRACE which logs all sql statements in scope to a text file, optionally together with. timing and bind information, logminer *will* enable you to construct valid SQL to replicate what your application is doing, but I don't believe it guranteed to be the same SQL as was actually issued.

If you have an app of any size whatsoever I'd strongly suggest that you trace a representative session and not the whole database (unless you have a huge SAN with loads of spare space). Traces get big quickly.

The best reference for setting SQL_TRACE or its associated event that I have seen is http://www.petefinnigan.com/ramblings/how_to_set_trace.htm

cheers

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com

p.s. I guess the answer is version specific since you've only been able to 
capture SQL since version 5 apparently.