OERR for Windows

Yes, Oracle does not ship oerr with Windows version. But some smart guys struggled to solve the problem. Here you can download the windows oerr implemented with Perl.

But, I’ve lived a long life without this tool. I just built up my own oerr written in 100% SQL.

One very simple but not useful way is to use following code.

dbms_output.put_line(sqlerrm(-1555)) ;

But this generate only the simple message, without cause and action information.

ORA-01555: snapshot too old: rollback segment number  with name "" too small

Following is how I built up my own oerr which is capable of showing the entire message.

1. Create directory where oraus.msg file lives(In this demo, I’m in Windows oracle database)

create or replace directory dir_oraus
as 'C:\Documents and Settings\exem'
;

2. Create external table to which oraus.msg file is loaded.

create table ext_oraus(
  line    varchar2(4000)
) organization external
(
  default directory dir_oraus
  access parameters
  (
    records delimited by newline
    fields terminated by '~'
  )
  location ('oraus.msg')
)
reject limit unlimited
;

3. Create permanent table to store the contents of oraus.msg file and load the contents into that table.

create table t_oraus (

  err_code  varchar2(5),

  message   varchar2(1000),

  texts     clob

);

declare

  v_err_code  varchar2(4000);

  v_message  varchar2(4000);

  v_texts    clob;

begin

  for r in (select line from ext_oraus) loop

    if substr(r.line, 6, 1) = ',' then

      -- record new data

      if v_err_code is not null then

        insert into t_oraus(err_code, message, texts)

          values(v_err_code, v_message, v_texts);

      end if;

      v_err_code := substr(r.line, 1, 5);

      v_message := substr(r.line, 14);

      v_texts := '';

    elsif substr(r.line, 1, 2) = '//' then

      v_texts := v_texts || r.line || chr(13)|| chr(10);

    end if;

  end loop;

  -- last record

  insert into t_oraus(err_code, message, texts)

          values(v_err_code, v_message, v_texts);

  commit;

end;

/

create index t_oraus_n1 on t_oraus(err_code);

4. Now we can do oerr just by selecting corresponding data from the table t_oraus

select err_code, texts

from t_oraus

where err_code = '01555'

;

ERR_C TEXTS
----- -------------------------------------------------------------------------
01555 // *Cause: rollback records needed by a reader for consistent read are
      //           overwritten by other writers
      // *Action: If in Automatic Undo Management mode, increase undo_retention
      //          setting. Otherwise, use larger rollback segments

5. The fact that the whole data is stored in RDBMS gives me the full flexibility, like

ed oerr.sql

select err_code, texts
from t_oraus
where err_code = lpad(to_char(&1), 5, '0')
;

@oerr 1555

ERR_C TEXTS
----- --------------------------------------------------------------------------
01555 // *Cause: rollback records needed by a reader for consistent read are
      //           overwritten by other writers
      // *Action: If in Automatic Undo Management mode, increase undo_retention
      //          setting. Otherwise, use larger rollback segments

Or

select err_code, texts
from t_oraus
where err_code like '0403%'
;

ERR_C TEXTS
----- ----------------------------------------------------------------------------
04030 // *Cause:  Operating system process private memory has been exhausted
      // *Action:

04031 // *Cause:  More shared memory is needed than was allocated in the shared
      //          pool.
      // *Action: If the shared pool is out of memory, either use the
      //          dbms_shared_pool package to pin large packages,
      //          reduce your use of shared memory, or increase the amount of
      //          available shared memory by increasing the value of the
      //          INIT.ORA parameters "shared_pool_reserved_size" and
      //          "shared_pool_size".
      //          If the large pool is out of memory, increase the INIT.ORA
      //          parameter "large_pool_size".

04032 // *Cause:  attempt to set workarea_size_policy to AUTO while
      //            pga_aggregate_target is not specified
      // *Action: before setting workarea_size_policy, set pga_aggregate_target
      //          to a value representing the total private memory available to
      //          the instance. This total is generally the total physical memory
      //          available in the system minus what is needed for the SGA minus
      //          what is needed for the operating system (e.g. 500MB)

04033 // *Cause:  The system had insufficient memory to grow the pool to the
      //          specified size.
      // *Action: Specify a smaller value to grow the pool.

04034 // *Cause:  The pool could not shrink to the specified size as it could not
      //          free memory.
      // *Action: Specify a larger value to which to shrink the pool to.

Very flexible, isn’t it?

I’m just still curios why Oracle doesn’t ship oerr for Windows. :)

Advertisements