To Set SQL Plus Prompt to the database Name of which we were logged in using login.sql

########################
 Task
########################

To Set SQL Plus Prompt to the database Name of which we were logged in.

Usually When we login to SQLPLUS, the prompt will be like below,

SQL>

And in many situations we need to check which database we are logged in. A Simple Solution is.

==================================================================================================
Step 1 : Use of login.sql
==================================================================================================

Locate login.sql file. The location for this file is $ORACLE_HOME/dbs

If the file is not available then create a file with the name login.sql and add the below content to it and save the file.

==================================================================================================
set termout off
spool set_prompt.sql
     select 'set sqlprompt '||instance||'>'
     from   sys.v_$thread;
     spool off
     start set_prompt.sql
set termout on
==================================================================================================

==================================================================================================
Step 2 : Now login and Check
==================================================================================================

Now login to the database and check.

oralin @ exampled01:/u01/app/oracle/product/11.2/db_2/dbs
> sps

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 8 04:42:17 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> conn test/test@oradup as sysdba
Connected.
oradup>
oradup>conn test/test@orawin as sysdba
Connected.
orawin>
orawin>

==================================================================================================