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.