I recently read an article that have an SQL prompt displyaing "user@hostname". As DBAs we frequently use SQL*Plus to perform daily administrative tasks. Often, you'll work on servers that contain multiple databases. Obviously, each database contains multiple user accounts. When connected to a database, you can run the following commands to verify information such as your username, database connection, and hostname:
SQL> show user;
SQL> select name from v$databae;
A more efficient way to determine your username and SID is to set you SQL prompt to display that information; for example,
SQL> SET SQLPROMPT '&_USER.@&_CONNECT_IDENTIFIER.> '
An even more efficient way to configure your SQL prompt is to have it automatically run the SET SQLPROMPT command when you log in to SQL*Plus.
Follow these steps to fully automate this:
1. Create a file named login.sql, and place in it the SET SQLPROMPT command.
2. Set your SQLPATH OS variable to include the directory location of login.sql. In this example the SQLPATH OS variable is set in the .bashrc OS file, which is executed each time a new shell is logged in to or started. Here is the entry:
export SQLPATH=$HOME/scripts
3. Create a file named login.sql in the HOME/scripts directory. Place the following line in the file:
SET SQLPROMPT '&_USER.@&_CONNECT_IDENTIFIER.> '
4. To see the result, you can either log out and log back in to your server or source the .bashrc file directly:
$ . ./.bashrc
Now, log in to SQL. Here is an example of the SQL*Plus prompt: If you connect to a different user, this should be reflected in the prompt
Setting your SQL prompt is an easy way to remind yourself which environment and user you're currently connected as. This will help prevent you from accidentally running an SQL statement in the wrong environment. The last thing you want is to think you're in a development environment and then discover that you've run a script to delete objects while connected in a production environment.
SQL> show user;
SQL> select name from v$databae;
A more efficient way to determine your username and SID is to set you SQL prompt to display that information; for example,
SQL> SET SQLPROMPT '&_USER.@&_CONNECT_IDENTIFIER.> '
An even more efficient way to configure your SQL prompt is to have it automatically run the SET SQLPROMPT command when you log in to SQL*Plus.
Follow these steps to fully automate this:
1. Create a file named login.sql, and place in it the SET SQLPROMPT command.
2. Set your SQLPATH OS variable to include the directory location of login.sql. In this example the SQLPATH OS variable is set in the .bashrc OS file, which is executed each time a new shell is logged in to or started. Here is the entry:
export SQLPATH=$HOME/scripts
3. Create a file named login.sql in the HOME/scripts directory. Place the following line in the file:
SET SQLPROMPT '&_USER.@&_CONNECT_IDENTIFIER.> '
4. To see the result, you can either log out and log back in to your server or source the .bashrc file directly:
$ . ./.bashrc
Now, log in to SQL. Here is an example of the SQL*Plus prompt: If you connect to a different user, this should be reflected in the prompt
Setting your SQL prompt is an easy way to remind yourself which environment and user you're currently connected as. This will help prevent you from accidentally running an SQL statement in the wrong environment. The last thing you want is to think you're in a development environment and then discover that you've run a script to delete objects while connected in a production environment.
Comments
Post a Comment