Re: Tracing SQL to find out what is causing hangs

> Not being an Oracle DBA myself (but experienced with other RDBMSes), but
> finding myself left supporting some Oracle systems while they are on
> holiday I need some help with a problem which has occurred.

Why do we see more and more of these caveats every day? Thankfully, DBAs get to take vacations, and I'm sure that they are well deserved ones too. But why isn't there a backup DBA to do these sorts of tasks? I constantly see threads that start with "Our regular DBA is on vacation and I am required to fix this problem while they are gone....." Hopefully management realizes that the business needs this sort of expertise around. Oracle is a complex thing. By the time someone gets up to speed to solve the problem, the regular DBA is probably back from vacation. And the problem needed to be fixed sooner.

Oops.....I must have taken a left turn at Albuquerque. Ok....back on track now.....

> What I'd like to do is monitor the SQL going through the system in the
> background to have a complete picture of what is happening up until the
> point of the hang.
>
> On "the other RDBMS" (OK, I come clean : DB2) I'd set up an event monitor
> to capture all SQL and write it into tables for analysis.
>
> How would I do something similar on Oracle ?

The DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION procedure can be used to start a trace in current session. You'll need to know the session's SID and SERIAL# which can be gleaned from V$SESSION. The resulting trace file(s) will be found in the directory denoted by the USER_DUMP_DEST param.

Have fun!
Brian

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"