Re: When do system events become effective?
- From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 4 May 2007 16:08:34 +0100
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@xxxxxxxxx> 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@xxxxxxxxxxxx> 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
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
