Oracle Tips and Tricks — David Fitzjarrell

September 3, 2008

To Err Is Human

Filed under: General,pl/sql — dfitzjarrell @ 14:23

Someone asked

“how oracle is keeping track of last few ora- errors”

and the answer is a fairly simple “it isn’t”. (Granted, Oracle does record the severe errors in the database alert log, but those aren’t all of the errors that can be generated by any stretch of the imagination.) It can, though, if you ask it to.

Since Oracle 8.1.6 system event triggers have been available, and one of those triggers is an AFTER SERVERERROR trigger, which can log most errors you encounter. Setting up your database to do that is a fairly simple task (the following works in 10.2 and later releases):

SQL> CREATE TABLE stats$error_log (
  2          err_dt          TIMESTAMP,
  3          db_user         VARCHAR2(30),
  4          msg_stack       VARCHAR2(2000),
  5          sqltxt          VARCHAR2(1000))
  6  tablespace tools;

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER log_server_errors
  2    AFTER SERVERERROR
  3    ON DATABASE
  4  DECLARE
  5            v_sqltext VARCHAR2(1000);
  6            nl_sqltext ora_name_list_t;
  7    BEGIN
  8            -- Capture entire error text
  9            FOR i IN 1 .. ora_sql_txt(nl_sqltext) LOOP
 10              v_sqltext := v_sqltext || nl_sqltext(i);
 11            END LOOP;
 12
 13            INSERT INTO STATS$ERROR_LOG
 14            (err_dt, db_user, msg_stack, sqltxt)
 15            VALUES
 16            (systimestamp,
 17             sys.login_user,
 18             dbms_utility.format_error_stack, v_sqltext);
 19
 20    END log_server_errors;
 21  /

Trigger created.

SQL>

Now you’re set to log most errors in the STATS$ERROR_LOG table:

SQL> select a.object_name
  2  from dba_objects a, dba_objects b, dba_objects c
  3  order by 1;
from dba_objects a, dba_objects b, dba_objects c
     *
ERROR at line 2:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP


SQL>

Let’s query that table and see what it reports:

SQL> select * from stats$error_log;

ERR_DT
---------------------------------------------------------------------------
DB_USER
------------------------------
MSG_STACK
--------------------------------------------------------------------------------
SQLTXT
--------------------------------------------------------------------------------
01-SEP-08 09.35.15.159854 AM
******
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
select a.object_name from dba_objects a, dba_objects b, dba_objects c order by 1


SQL>

We’ve preserved the error stack and the ‘offending’ SQL along with the executing USER (obscured for security reasons) and the timestamp when the error was logged (which is approximately when the error occurred). Nice.

How many errors can you preserve in this log table? It all depends upon how much disk space you have to use. It should go without saying (but, hey, I’ll say it anyway) that this table needs to be monitored so it doesn’t become unwieldy; such data needs to be examined on a regular and fairly frequent basis so that problem areas can be addressed quickly and hopefully resolved.

Which errors won’t you capture? It appears to be a short list:

ORA-01403: no data found 
ORA-01422: exact fetch returns more than requested number of rows
ORA-01423: error encountered while checking for extra rows in exact fetch
ORA-01034: ORACLE not available
ORA-04030: out of process memory when trying to allocate string bytes (string, string)

Pretty much any other error should be logged in the table for examination at a later time, which is convenient for the DBA as sometimes he or she cannot address an error when it occurs, or the application doesn’t pass such error text through to the end-user. And you, as a DBA, can’t address errors of which you’re not informed.

An AFTER SERVERERROR trigger may not be something you want to have running all of the time, but it is nice to know you can fire one up and capture problem statements and the errors they create should you need to do so. Just as a carpenter doesn’t use a hammer for every task database tools like event triggers aren’t the solution to every problem. But it is nice to know they’re available when and if you need them, because having the right tools for the job makes the task so much easier.

Advertisements

1 Comment »

  1. Good job~ thank you for share this post, but your script cannot work on some errors like ORA-01401: inserted value too large for column; so,I do a little enhanceplease refer to following post@http://nil.gcamay.com.tw/blog/post/61/

    Comment by NIL — March 5, 2009 @ 10:12 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: