Monday, December 14, 2009

oerr: Oracle Error Tool

The Oracle Error utility (oerr) is a handy tool that most Oracle DBAs are likely well aware of, but I have found that many developers are not aware of. This is one of those quick and simple tools that belongs in the category of Faithful Old Development Tools that I have blogged on before.

The oerr tool is an awk-based tool available on Unix and Linux as part of Oracle database installations. The tool is simple to use and provides a brief description of the cause of a particular Oracle error code and an action or multiple actions one can take to address the database error.

To use the tool in a Unix or Linux environment with an Oracle database appropriately installed, one need only run the command like this: oerr AAA NNNNN where oerr is the command, "AAA" represents the Oracle error code prefix (such as "ORA" for all Oracle error codes beginning with ORA-), and the "NNNNN" represents the numeric portion of the Oracle error code. For example, if the Oracle error ORA-01002 is encountered, the command oerr ora 1002 will print out the likely cause or causes of this particular error along with an action or actions to take to address the error.

The handy awk-based oerr tool is not available with Windows installations of Oracle database. However, there are numerous online listings of the Oracle error codes with their causes and actions (see some in the "Additional Resources" section below). There are also variations of the Oracle-provided oerr tool available that do run on Windows. Examples of these include Perl-based Windows oerr for Oracle, SQL-based OERR for Windows, gawk-based oerr "tool" for Windows, a Java-based approach, the Firefox browser add-on oerr - Oracle Error Code Lookup and many web-based tools such as Web Oerr 11g.


Conclusion

The simple-to-use command-line oerr tool can be useful for quick determination of what is causing a particular Oracle error condition and the possible action or actions that can be taken to remedy the situation.


Additional Resources
oerr
Oracle Wiki - oerr
Oracle Messages and Causes
Ora-Code
Ora-Error!
Oracle Error Codes
Oracle Error Codes
Web Oerr 11g

No comments: