OERR – In search of Error Messages/Events

1

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 was already 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.

;-)

Share.

About Author

Marco Gralike, working for AMIS Services BV as a Principal Oracle Database Consultant in the Netherlands, has experience as a DBA since 1994 (Oracle 6). Marco is also eager and skillful in other fields, like Operating System Administration and Application Servers, mainly to find working, performing solutions. Marco has been specializing in Oracle XMLDB, since 2003, focusing on his old love, database administration and performance. He is an Oracle XMLDB enthusiast ever since. He is also a dedicated contributor of the Oracle User Group community, helping people with their steep XMLDB learning curve. To this purpose, Marco also devoted his personal blog site to XMLDB and other Oracle issues. Marco is a member of the OakTable network and an Oracle ACE Director (specialization Oracle XMLDB).

1 Comment