Jan 6, 2018 4:33:00 PM by JP Vijaykumar
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.
$ cat glogin.sql
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.
While testing this script, I encountered these issues in some of our dbs.
1. 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.
2. 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 220.127.116.11.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 >
Written by JP Vijaykumar