OERR – In search of Error Messages/Events

Sometimes you want to search for Oracle database messages, their meaning or you are in search of specific database event settings.

For instance, a long time ago i was searching for an event that would trace when an datafile would expand / autoextend. I encountered latching problems on a Siebel system based on an new Oracle Failsafe environment (Windows 2000 cluster). I wanted to be sure that these latching problems weren’t introduced by the newly introduced datafile AUTOEXTEND feature.

So i put in a TAR on metalink with the request if there was a way to trace, eq. an event setting, when a datafile extended. This way i could cross-reference this timestamp to the timestamps i was registering my latch problems. The reponse took a long time. I was in need for an answer, so i created a workaround via a PL/SQL procedure. This procedure was executed via the database job scheduler. Every time a datafile extended, the timestamp, SCN, extend growth etc. was picked up by this procedure, and the data was stored in a table.

Lately i am in search of XML DB knowledge, more specific, DBA/database specific XML DB knowledge (object performance/sizing/XML Schema tuning, etc. stuff). In the new Oracle 10g Release 2 manuals, i came across the event setting 31098: “Internal event to turn on XDB tracing”. So i wondered if there were more of these settings. Apparently this setting wasalready applicable in Oracle 10g Release 1.

I have a small script called OERR, like the Oracle message utility under UNIX. It does the following:

 
SQL> @oerr 31098

Error 31098 is: ORA-31098: Internal event to turn on XDB tracing

The SQL code for this script:

prompt

set serveroutput on size 1000000

set feedback off

exec dbms_output.put_line('Error ' || &&1 || ' is: ' ||sqlerrm(-1 * &&1));

prompt

undefine 1

set feedback on

Wondering if i couldn’t do more with this, when using it with Oracle collections, i came up with the following:

 
SQL> @search_oerr

Enter string to search: XDB


RESULT:

ORA-31000: Resource '' is not an XDB schema document
ORA-31004: Length  of the BLOB in XDB$H_INDEX is below the minimum
ORA-31098: Internal event to turn on XDB tracing
ORA-31099: XDB Security Internal Error
ORA-31100: XDB Locking Internal Error
ORA-31112: fail to  for  port using xdb configuration
ORA-31113: XDB configuration may not be updated with non-schema compliant data
ORA-31114: XDB configuration has been deleted or is corrupted
ORA-31115: XDB configuration error:
ORA-31153: Cannot create schema URL with reserved prefix 'http://xmlns.oracle.com/xdb/schemas/'
ORA-31155: attribute  not in XDB namespace
ORA-31179: internal XDB event for ftp test harness


12 rows selected of 59989 records

PL/SQL procedure successfully completed.
 

The SQL statements for this report are:

 
-- Find certain events or error numbers

set serveroutput on size 1000000
set array 1
set long 10000
set trimspool on
prompt
accept XXX char prompt 'Enter string to search: '

DECLARE

TYPE statement IS RECORD
(r_statement varchar2(1000));

TYPE statement_stack IS TABLE OF statement
INDEX BY binary_integer;

showstring statement_stack;
t number:=0;

BEGIN
  dbms_output.put_line(chr(10));
  dbms_output.put_line('RESULT: '||chr(10));
  showstring.delete;
  for i in 0..60000 loop
     showstring(i).r_statement := (sqlerrm(-1 * i));
     if upper(showstring(i).r_statement) like upper('% &&XXX %')
     then
        dbms_output.put_line(showstring(i).r_statement);
        t:=nvl(t,0)+1;
     else
        null; --> you should replace this with some usefull code
     end if;
  end loop;
  dbms_output.put_line(chr(10));
  if t=0 then --> just for fun
     dbms_output.put_line('no rows selected');
  elsif t=1 
  then
    dbms_output.put_line('1 row selected');
  else
    dbms_output.put_line(t||' rows selected of '||(showstring.COUNT-t)||' records');
  end if;
END;
/

undefine XXX

I hope you can use it. I know it’s not hightech but just like the old “Tales from the script” SQL scripts on Metalink (it’s still there), it’s a starting point for further improvement.

😉