Re: When do system events become effective?

A couple of people have pointed out that my old site does not render correctly in all browsers. I've therefore dug out the text below. You'll have to download the zip file yourself if you want it.

We have one of those irritating issues with one of our Oracle systems, periodically this system, or rather a process using it, will encounter the server error ora-01652, this shows up in the alert.log monitoring that we do.Whilst the cause of the error is well known (generally running out of sort space), we had no reports from end users of the problem, and neither could we tie it down to a scheduled process. So the question remained how do you determine which session and/or sql is the source of an error in the alert.log.

One approach would obviously be to consider that as no-one is complaining, and no scheduled or background tasks appear to be failing, that this is not a business problem at all. If you don't want to do this, I knocked up a quick and dirty monitoring tool that allows you to capture arbitrary server errors into a table along with the sql that caused it. The technique I use takes advantage of http://tinyurl.com/26la4 System Events which I believe are new or certainly extended in 9i. These events are a compelling alternative to the traditional use of the USERENV context for this class of task.

The scripts are available subject to the usual use at your own risk and common sense guidelines at
http://www.niall.litchfield.dial.pipex.com/scripts/utils/server_error.zip . Extract the files into a directory of your choice, read all the scripts first - take note particularly that the script creates a UTILS user with create session rights, so you may wish to revisit this after the install - and use errInstall to install the user and trigger.

Insert error codes as needed into monitored_events to start the monitoring. Delete from monitored_events to stop monitoring and disable the trigger to turn off monitoring altogether.

On 5/3/07, Niall Litchfield <niall.litchfield_at_gmail.com> wrote:
> Uwe,
>
> Does the approach at
>

http://www.niall.litchfield.dial.pipex.com/2004/06/we-have-one-of-those-irritating-issues
> help? Note larry wolfson of this list pointed out that in 9i don't try
> to monitor errors not caused by sql statements with this approach.
>
> cheers
>
> Niall
>
> On 5/2/07, Uwe Küchler <uwe_at_kuechler.org> wrote:
> > Oracle 9.2.0.6, HP-UX 11.11
> >
> > Hello world,
> > today I was trying to track down the SQL causing a full TEMP tablespace
by
> > tracing the 1652 event. I tried the following commands:
> >
> > - ALTER SYSTEM SET EVENTS '1652 trace name errorstack';
> > - ALTER SYSTEM SET EVENTS = '1652 trace name errorstack';
> > - ALTER SYSTEM SET EVENTS = '1652 trace name context forever, level 12';
> > - oradebug event 1652 trace name errorstack
> >
> > on a running system, but no trace files have been generated, although
the
> > alert log was flooded with this event after enabling trace.
> >
> > I had the same effect with the 10046 event a while ago and supposed that
> > it only comes in effect when a new session is started, but then
"oradebug"
> > would be usesless in this case. This cannot be. Unfortunately (as so
> > often) I didn't have the time to research, but now i need some input
from
> > you folks!
> >
> > So, why is there no tracefile after enabling trace and clearly seeing
the
> > traced event happen?
> >
> > Regards,
> > Uwe
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l