Oracle : The oerr Utility (Oracle Error)

• The oerr utility (oracle error) is provided only with Oracle Database on UNIX platform
• oerr is not an executable, but instead a shell script that retrieves messages from installed message files
• oerr is not provided on windows, since it uses “awk” commands to retrieve the requested text from the file

Syntax

oerr

where

facility is prefix to error number, this includes ORA, PLS, EXP etc.

The error is actual error number

For example

$ oerr ora 01652

01652, 00000, "unable to extend temp segment by %s in 

tablespace %s"

// *Cause:  Failed to allocate an extent of the required number of blocks for

//          a temporary segment in the tablespace indicated.

// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more

//          files to the tablespace indicated.

$ oerr EXP 00008

00008, 00000, "ORACLE error %lu encountered"

// *Cause:  Export encountered the referenced Oracle error.

// *Action: Look up the Oracle message in the ORA message chapters of this

//          manual and take appropriate action.

Output shows “”Cause” of the error and “Action” recommended.

How oerr works

• To best understand how oerr works, you can review the shell script oerr.ksh available at $ORACLE_HOME/bin
• The commands in the oerr.ksh file confirms $ORACLE_HOME is set and if not utility will terminate.
• Facility information is read from facility file located in $ORACLE_HOME/lib/facility.lis. below is some portion of facility.lis file

aud:ord:*:

amd:cwmlite:*:

av:pfs:*:

bbed:rdbms:*:

brw:browser:*:

clsr:racg:*:

ds:office:*:

dsp:office:*:

dsw:office:*:

dsz:office:*:

ebu:obackup:*:

evm:evm:*:

exp:rdbms:*:

fmc:forms40:*:

iac:forms:*:

iad:forms:*:

lcd:rdbms:*:

oao:office:*:

obk:obackup:ebu:

omv:office:*:

opw:rdbms:*:

ora:rdbms:*:

osn:network:*:

osnq:network:*:

Facility file contains three mandatory data items and one optional data item

Mandatory

•   Facility
•  Component
•  Name of alias for component if any, otherwise a * will be used

In above file facility.lis ora is facility, rdbms is component and it does not have alias so *

Optional

•   Description

Using the facility name provided on command line, oerr retrieves the component for that facility

For example 

oerr ora 01652, oerr uses rdbms component, using this information, appropriate message file can be retrieved

Msg_File=$ORACLE_HOME/$Component/mesg/$(Facility)us.msg

for ORA errors

Msg_File=$ORACLE_HOME/rdbms/mesg/oraus.msg

Once this path and file is retrieved the content of the will be provided with cause and action for each error in facility

oerr on Windows

• oerr  is only available on UNIX but it does not take much code to access message file and display the message on windows
• To do this, the actual message file needs to copied to windows machine and placed in same directory as java program.
• The java program (oerr.java) reads the message and display text associated with error code.
• Java program reads the message file line by line until it encounters the actual error code

For example

C:\oracle\bin\java oerr ora 01652

Create custom Message Files

There are number of cases, where oerr does not show up any message, for such error codes, we can create own customized message file with error code

For example

$ oerr ora 00942

00942, 00000, "table or view does not exist"

// *Cause:

// *Action:

Following can be used to show customized message file

oerr technofile 00942

Where technofile is customized error message file. A customized message file can be created with below steps

1. Add the facility in facility.lis file
2. Create a directory that contains new message file
3. Create the actual message file

Alternatives for oerr 

ORACLE TechNet  - http://technet.oracle.com

ORACLE Metalink – http://support.oracle.com