Change the Prompt

How do you change the prompt in SQL PLUS

set sqlprompt "&&prompt_dbname> "

or

set sqlprompt &prompt_dbname>

This will prompt you to enter the DB Name

Enter value for prompt_dbname: MyOracleDB

MyOracleDB>

With the above step you will have to repeat this every time you log in; But how do you avoid that?

When you are using multiple database instances you may get lost where and which database you are in: It is always a good idea to have the SQL Prompt to say what you are connected to. To do this:

Step 1: Create a new file called “login.sql” in the same folder as sqlplusw.exe resides

Step 2: Copy the below content into the “login.sql” file

column user_sid new_value sql_prompt
select lower(user) || '@' || '&_CONNECT_IDENTIFIER' user_sid from dual;
set sqlprompt '&sql_prompt> '

clear screen;
set serveroutput on;

There you go, Now everytime you log in you would see the user and Prompt

scott@MyOracleDB>