Oracle: Change the SQL Prompt with Glogin.sql

DBAs usually work with so many servers, databases at the same time. They toggle from one screen to another within few seconds. There are a lot hidden opportunities where you can make mistakes. Being a superuser  brings in more responsibilities and adds up cautions.

Following are some best practices to ensure you are on right database before you do any critical task like shutdown or drop is very relevant :

1. Login as SYSDBA only when required, otherwise create a database user and grant DBA role.
2. Use shell script to perform task like shutdown, startup of databases.
3. Change the color of putty sessions for example Red for Production, Blue for QA, Yellow for Test.
4. SQL Prompt: Change the SQL prompt on the database to show the Database Name/SID with user whom you have connected as. Oracle by default calls login.sql or glogin.sql whenever 'sqlplus' command is given on a UNIX machine. The script login.sql or glogin.sql is located in $ORACLE_HOME/sqlplus/admin. You can add a line to query from v$instance table something like below:

select host_name||':'||user||' @ '||UPPER(instance_name) X from v$instance;

The problem with the above SQL in login.sql is that SQLPLUS will hang if the database is down. When database is down, you cannot query any v$ views. The other way which does the same but safe way is by the command below:

The SQL prompt will be SYSTEM@TESTDB SQL> .