Dion Cho – Oracle Performance Storyteller

We are natural born scientists

OERR for Windows

with one comment

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. :)

Written by Dion Cho

March 4, 2009 at 8:52 am

Posted in Misc.

Tagged with ,

One Response

Subscribe to comments with RSS.

  1. Thank you for sharing Dion.

    Can you also add archive of your blog so we can check all the posts instead of being lost in tags.

    coskan

    March 17, 2009 at 5:56 pm


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

%d bloggers like this: