Coskan’s Approach to Oracle

April 1, 2007

Viewing Error Definitions from SQL*plus or Command Line

Filed under: Tips — coskan @ 1:05 pm

Suppose that you faced with an error when you execute a command. What you do to first is usually to look at the error codes documentation of Oracle and read the action you can take against the error. There is an another way to do it in Unix environments using OERR utility. This useful utility could lower the steps of your error code solution research. It is not an executable, it is a shell script and it is flexible for customizing. OERR is located under $ORACLE_HOME/bin directory with a name oerr.ksh. It reads the file $ORACLE_HOME/lib/facility.lis to show the messages. This file contains the paths to messages for the given facalities. (for example ORA errors are related with rdbms folder and the facility.lis files directs the oerr command to the rdbms/mesg/* folder to get the definition of the error code). You can add your own message files to the facility.lis for customized errors or change the definitions of the predefined error codes.

The syntax to call the oerr is below;

#oerr <facility> <error number>

    

     
Usage Samples; (! is used to call the command from the OS command line while you are in sql*plus)
SQL> !oerr ora 07400
07400, 00000, “slemtr: translated name for the message file is too long.”
// *Cause: The name for the message file overflows internal buffer.
// *Action: Try making the complete path-name of the message file shorter
// by reorganizing the directory hierarchy.

=>default ora-01652

SQL> !oerr ora 01652
01652, 00000, “unable to extend temp segment by %s in tablespace %s”
// *Cause: Failed to allocate an extent for temp segment in tablespace.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.

=>custimized ora 01652 (I have edited the $ORACLE_HOME/rdbms/mesg/oraus.msg file )

SQL> !oerr ora 01652

// *Cause: Failed to allocate an extent for temp segment in tablespace.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.Please be carefull when you are creating TEMPFILES!!!!!

Note: OERR comes with oracle installation on Unix environments and is not implemented for windows by default because of its nature which depends on the awk command however you can install freeware programs for windows too (look at the references)

References Used:

http://www.oracleutilities.com/OSUtil/oerr.html

Theme: Silver is the New Black. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 203 other followers