In this tutorial, I am giving an example to how to set SQL prompt in the USER@DATABASE> format in Oracle.
Set SQL Prompt in Oracle Example
After logging into the SQL*PLUS, give the following command to set the SQL prompt as user@database>:
set sqlprompt '&_user.@&_connect_identifier.> '
Suppose you logged in as user SCOTT and Oracle database name is ORCL then the prompt would be SCOTT@orcl> as shown in the below image:
But I understand that it is hard to remember the syntax of the command and to give this command every time you log in to SQL*Plus. So here is how you can set this setting permanently for SQL*Plus.
Whenever SQL*Plus starts, it looks for the glogin.sql file for the custom settings. The location of the glogin.sql file is ORACLE_HOME/sqlplus/admin, for example: F:\app\Administrator\product\11.2.0\dbhome_1\sqlplus\admin. To set the SQL prompt, open the glogin.sql file from this folder and paste this command (SET SQLPROMPT '&_user.@&_connect_identifier.> ') into the file and save it.
Now if you open the SQL*Plus and after logging in you will see the custom prompt.
Note: Maybe you have installed multiple Oracle homes and when you will run the SQL*Plus from the command line by typing SQLPLUS in CMD then maybe it will run from the location set in the PATH variable in the Windows. So make sure that you are running the SQL*Plus from the Oracle home where you configured its prompt setting.