Wednesday, June 09, 2004
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 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 here. 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.
0 Comments
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 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 here. 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.
0 Comments:
Post a Comment
