Unlock Oracle Error Codes with the oerr Shell Script: A Deep Dive

The oerr command is a handy Oracle utility that lets DBAs quickly retrieve the description, cause, and recommended action for any ORA error without memorizing error numbers.

The script resides in $ORACLE_HOME/bin/oerr and, despite its binary‑like permissions, is a plain shell script. For example:

# Usage: oerr facility error
# This shell script is used to get the description and the cause and action
# of an error from a message text file when a list of error numbers are passed

It reads a configuration file $ORACLE_HOME/lib/facility.lis to translate the first argument (the facility) into a component name. The script contains a line such as:

Fac_Info=`grep -i "^${Facility}:" $Facilities_File 2> /dev/null`

When the facility is ora, the lookup yields rdbms:

oracle@db117 ~]$ grep -i "^ora:" $ORACLE_HOME/lib/facility.lis
ora:rdbms:*:

The facility.lis file uses a colon‑separated format facility:component:rename:description, e.g.:

acfs:usm:*:

amdu:rdbms:*:

Once the component is known, the corresponding message file is located under $ORACLE_HOME/<em>component</em>/mesg. For the rdbms component, the file oraus.msg contains entries such as:

64477, 00000, "Multiple token tables are not supported."
// *Cause:   An attempt was made to create a new token table. If encountered
//           during an import, a critical patch was possibly missing on the
//           export database.
// *Action:  Use the default token table. If encountered during an import,
//           apply the appropriate patch on the export database and try the
//           export and import again.

64621, 00000, "SQL statement length (%s) exceeds maximum allowed length (%s)"
// *Cause:  An attempt was made to issue a SQL statement that exceeded the
//           maximum allowed length of a statement.
// *Action: Modify the SQL statement or the views to which it refers to fit
//           within the maximum length or split the SQL statement.

To retrieve a specific error, the script first normalizes the error number with sed to remove leading zeros: Code=`echo 64621|/bin/sed 's/^[0]*//'` It then uses an awk program to print the matching entry and its comment lines:

awk "BEGIN { found = 0; }
/^[0]*$Code/    { found = 1; print ; next; }
/^\/\//         { if (found) { print; } next; }
{ if (found) { exit; } }" $ORACLE_HOME/rdbms/mesg/oraus.msg

The command outputs the full error record, including the cause and action sections, allowing DBAs to understand and resolve the issue immediately.

By following this approach, you can customize the lookup process or build your own tools that query Oracle error messages in a way that fits your workflow.