Changing the prompt dynamically in SQL*Plus
This is just a simple trick which is not well-known but makes life easier when using the good ol’ SQL*Plus commandprompt (and many people still do!).
If you set the SQLPROMPT environment variabele (either manually or in the login.sql) the standard prompt is replaced by the name of the instance:
SQL> set sqlprompt "_CONNECT_IDENTIFIER>"
I use this in combination with the login.sql. I’m not sure if this was a feature before Oracle 10g, but recently I noticed that the login.sql is executed after every login, and not only when you start a new prompt. And that means that everytime you change your connection, the sqlprompt also changes according to the value of _CONNECT_IDENTIFIER. This probably will prevent many mistakes because you’re typing update-statements in the "wrong" environment!
These substitution variables are available from SQL*Plus 9.2 onwards. Other values you can use for sqlprompt are _USER, _DATE, _O_VERSION and _O_RELEASE. They speak for themselves I guess!
mydb> set sqlprompt "_USER _CONNECT_IDENTIFIER> "<br />SCOTT mydb>