Re: Getting Oracle to tell me why it's unhappy

Well, you can use svrmgrl to invoke oradebug to set the event in a remote session, but you have to know the operating system process id, so the session would be running anyway and you might miss the problem statement. But, with a third party app you may have no choice, so here is how you do it:

Figure out the operating system process id for the connected session in question. If you know that every session will eventually get the error anyway, just pick one. You can use v$session and v$process to track down a likely process. Join v$process and v$session by v$process.addr = v$session.paddr. Dig through the v$session columns to find the likely candidate, either by machine name, username or whatever else you can find to narrow it down.

Having the os process id, run svrmgrl, connect internal. From the prompt:

oradebug setospid <os process id you found> oradebug unlimit
oradebug event 1401 trace name processstate level 10

then exit the svrmgrl session. The event will be set for the target process (if you are not using MTS, that is) and the "unlimit" command ensures that a big trace file can be written without being truncated.

I do not recommend doing this for more than one session, as it is a bit of a hassle and produces lots of trace file info. One session is all you need to track the baddie down anyway.

I do not have a 7.3 instance handy to test the syntax, so double check it before the actual need. You should get a simple prompt back or a message like "statement processed", but no error code. Note that oradebug commands do not like a final ";", so don't put one on the end or you will get an error. Note the spelling of "processstate" with three "s" in a row. Important.

• ricky

Stan Brown wrote:
>
> In <3AF7416D.B9DE83A8_at_more.net> Ricky Sanchez <rsanchez_at_more.net> writes:
>
> >One minor additional point: this technique will only produce a trace file on
> >the error event itself. It will not produce a voluminous spew of text for
> >either other sql statements or other sessions. An obvious approach would be to
> >use the sql trace trace facility, but then you have mountains of trace files
> >and significant overhead until the actual offending event is encountered. This
> >error trap technique is much simpler and can be done in a production
> >environment.
>
> Tes, that's important on this one. While it's not the "produvtion"
> instance, it's a full blown simualtion with _lot's_ of data going inot
> it.
>
> Is there anyway to turn on this trap _without_ shuting the DB down?
>
> Thanks, again.
Received on Tue May 08 2001 - 11:14:59 CDT