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

About these ads

1 Comment »

  1. I have developed a Excel file which has a database of Oracle errors and can help to quickly lookup Oracle errors and its details.

    Please find it here.

    http://sanjeev-oracle-world.blogspot.com/2007/07/finding-details-of-oracle-errors.html

    Regards
    Sanjeev

    Comment by Sanjeevsapre — July 7, 2007 @ 3:44 pm


RSS feed for comments on this post. TrackBack URI

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

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 203 other followers

%d bloggers like this: