Changing the SQL Prompt to reflect the user and db connected to:

Ever wonder which database you connected to with what user when you are busy changing the connections in SQL*Plus?

Here is the simple trick that shows you the user you connected as on the current database. Also note that the values will change automatically when you make another connection from the same window using "connect".

set sqlprompt "_user'@'_connect_identifier: SQL>"

SQL> set sqlprompt "_user'@'_connect_identifier>"

HR@test: SQL>

Now change the connection:

hr@test: SQL> connect san@prod

Enter password:

Connected.

SAN@prod: SQL> connect san@test2

Enter password:

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect

descriptor

Warning: You are no longer connected to ORACLE.

@: SQL> 

Hope this trick comes in handy.

BTW, this trick is from 10g onwards only.