Wednesday, May 16, 2012

This is how i customize my sqlplus prompt

I often use sqlplus to perform my daily administrative tasks and i work on multiple servers that contain multiple databases, each database contains multiple accounts.
When connected to a database, i need to know my username and my database connection to reassure me that I point to the correct database in order to avoid errors that could be catastrophic.

To customize your sqlplus prompt
  • Navigate to the directory $ORACLE_HOME/sqlplus/admin
  • Create a file login.sql (or edit the file if it already exists) using vi on unix / Linux or notepad on windows
     The next time you will login your sqlplus prompt will look like this : user@alias

Here is a complete list of SQL*Plus variables that you can use to customize your prompt.

There are eight variables defined during SQL*Plus installation. These variables only differ from user defined variables by having predefined values.
Table 12-3 Variables Predefined at SQL*Plus Installation
Variable Name Contains
_CONNECT_IDENTIFIER Connection identifier used to make connection, where available.
_DATE Current date, or a user defined fixed string.
_EDITOR Specifies the editor used by the EDIT command.
_O_VERSION Current version of the installed Oracle Database.
_O_RELEASE Full release number of the installed Oracle Database.
_PRIVILEGE Privilege level of the current connection.
_SQLPLUS_RELEASE Full release number of installed SQL*Plus component.
_USER User name used to make connection.

No comments:

Post a Comment