Date Jan 4th 2018

As a dba, I will be executing various scripts in multiple databases.

Mostly in non-productions databases.

 

In the event, if any of these scripts were executed inadvertently in the production database, it will be disastrous.

 

There is always a possibility of executing scripts inside a wrong window/terminal, completely wiping out schemas/databases.

 

To deal in such risky situations, I modified the glogin script and color coded the display of environment details as sqlprompt.

Before executing any command, I just look at the prompt and make sure, I am logged into the right database.

 

cd $ORACLE_HOME/sqlplus/admin

$ cat glogin.sql

define _editor=vi

column global_name new_value gname

set termout off

/***PLS MODIFY THE BELOW SQL AS PER YOUR REQUIREMENT OF VARIABLES AND COLOR ***/

select   --chr(27)||'[32m'||chr(27)||'[1'||to_char(0)||'m'|| --GREEN  COLOR

         --chr(27)||'[33m'||chr(27)||'[1'||to_char(0)||'m'|| --YELLOW COLOR

         --chr(27)||'[34m'||chr(27)||'[1'||to_char(0)||'m'|| --VIOLET COLOR

           chr(27)||'[35m'||chr(27)||'[1'||to_char(0)||'m'|| --PINK   COLOR

upper(sys_context('USERENV','HOST'))||'-'||sys_context('USERENV','DB_NAME')||'-'||sys_context('USERENV','DB_UNIQUE_NAME') global_name from dual;

/***PLS MODIFY THE ABOVE SQL AS PER YOUR REQUIREMENT OF VARIABLES AND COLOR***/

set termout on

set sqlprompt '&gname> '

 

 

The glogin.sql script provided by Oracle is just right for most of the environments.

I only modified the display varialbes in my sql prompt and added required color display as per my requirement.

 

Pls modify the script as per your requirement and test it thoroughly in a lower environment, before using in production dbs. 

 

Readers' discretion is advised.

 

References:

http://www.adp-gmbh.ch/ora/sqlplus/login.html

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:446220075876

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm

https://blog.tanelpoder.com/files/scripts/demos/xterm-color.sql --FOR COLOURED SQL DISPLAY

https://stackoverflow.com/questions/35909900/coloring-and-customising-sqlplus-prompt-in-shell

http://orasql.org/scripts/colors.sql

http://oracle.ninja/color-your-sqlplus-scripts/

While testing this script, I encountered these issues in some of our dbs.
01) while connecting to one db, Oracle complained with the following error:

--There is a size limit of 50 characters on the glogin string display.
--If this limitation exceeds, you get the following error:
"string beginning "'s too long. maximum size is 50 characters."

--I reduced the length of the displayed string with substr function.

02) When you are connecting to an idle instance as sysdba
and trying to startup the db, Oracle displayed the message
"Enter value for gname:".
I supplied the $ORACLE_SID name, connected to idle instance and started the db.
This problem will not arise when you are connecting to a running db.

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 8 16:31:43 2018

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

Connected to an idle instance.

Enter value for gname: <ENTER DB_NAME >