The Smallest Details Are the Ones that Make You Better

Posted in: Technical Track

One of the things I love about my career (Oracle DataBase Administrator) , is how fragile it is on a day in and day out basis. I can say with certainty that every day I turn on my laptop to start working can be my last day working as a DBA. This is not because I want it to be; it’s because unlike many jobs out there, the smallest mistake can be my last  in my current job.

Believe me, I am not under appreciating other jobs or professions out there, but the level of stress that this job has at its peak moments is comparable to being a surgeon performing open heart surgery or an air traffic controller with two airplanes that are heading for a collision.

Just imagine this: If a mistake is made, a surgeon might not have the correct data needed for the surgery, or the traffic controller may not have the electric power to co-ordinate his or her maneuvers. Putting it more lightly, we are responsible for having your bank account statements up to date, basically your money. Of course we are the ones who have helped you maintain the history of  those online games you played, so you can claim yourself the king of Warcraft or Madden.

What I want to get to is that if you are starting in this job or have been doing it for so long, you are only as good as your last 5 minutes, because a single mistake can be your last. So what you want to do is take the necessary steps to avoid any mistake in the environment you are working. This is what we at Pythian call being a FIT-ACER DBA.

The best way that I have found to achieve or be closer to this is by doing the following in the tools I work with:


The following configuration will help you put your words in Red with a black background, helping you distinguish a Development and a Production environment

Windows --> Colours --> Default Foreground
Red ->255, GREEN ->0 , BLUE->0


This tool is a little more discreet , but the best thing you can do is go in your connection window, search for the “Color” column, and switch all your production connections to the color red.


The best thing that you can do is in the glogin.sql, which is in $ORACLE_HOME/sqlplus/admin. Add the following line.

SET SQLPROMPT '&_connect_identifier >'

You can also do what Uwe Hesse uses.

Put this in a script called whoami.sql

set serveroutput on
dbms_output.put_line('USER: '||sys_context('userenv',-
dbms_output.put_line('SESSION ID: '||sys_context('userenv',-
dbms_output.put_line('CURRENT_SCHEMA: '||sys_context('userenv', -
dbms_output.put_line('INSTANCE NAME: '||sys_context('userenv', -
dbms_output.put_line('DATABASE ROLE: '||sys_context('userenv', -
dbms_output.put_line('OS USER: '||sys_context('userenv', -
dbms_output.put_line('CLIENT IP ADDRESS: '||sys_context('userenv', -
dbms_output.put_line('SERVER HOSTNAME: '||sys_context('userenv', -
dbms_output.put_line('CLIENT HOSTNAME: '||sys_context('userenv', -

And your result will be the following:

OS USER: oracle
SERVER HOSTNAME: servidor1.localdomain
CLIENT HOSTNAME: servidor1.localdomain


For those of us who handle UNIX and Linux environments, one of our best friends to help identify where we are and which is the current ORACLE_SID we have set in our environment is the PS1 variable. With the example below, you can see several things which can help you identify where you are:

`echo ${LOGNAME}`@`hostname` [\${ORACLE_SID}] \${PWD}
`echo ${LOGNAME}` $ "
export PS1
  • Example without PS1 environment set as above
[[email protected] ~]$ echo "Hi"
  • Example with PS1 environment set as above
[email protected] [TESTDB] /home/pythian/working/antunez
pythian $ echo "Hi"

SQL Developer

This Oracle tool allows you to put all your connections into folders, which will allow you to easily identify which type of connections you have.

Right Click on the connection name --> Add To Folder

All of these tips might not seem like a lot, but believe me that when you are facing one of those stressful moments , you want to make sure that you are working where you should be working. Believe me: This little grain of salt will help you maintain your job at least for one more day. :)



Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Currently I am an Oracle ACE ; Speaker at Oracle Open World, Oracle Developers Day, OTN Tour Latin America and APAC region and IOUG Collaborate ; Co-President of ORAMEX (Mexico Oracle User Group); At the moment I am an Oracle Project Engineer at Pythian. In my free time I like to say that I'm Movie Fanatic, Music Lover and bringing the best from México (Mexihtli) to the rest of the world and in the process photographing it ;)

7 Comments. Leave new

Ludovico Caldara
April 7, 2013 4:43 pm

Nice, but you can still improve your PS1, IMO:
Also, taking the format [email protected]:path will allow you to quickly copy&past paths for scp command lines. ;-)

Rene Antunez
April 10, 2013 1:13 am


IMHO there will be always be a better way, but what ever way makes you conscious of where you are working and helps you do your job better, that is what you should use

My Possible Pasts » Blog Archive » Using roles to prevent disaster
April 8, 2013 1:12 pm

[…] was reading Rene Antunez’s post about some things you can do with tools you use to help make you aware when you are in Production. […]

Rene Antunez
April 10, 2013 1:14 am

Thank you for the mention, and as you said it is not bullet proof, but that is another way to make you aware of what you are doing


Nice one and how about some aliases?
Warning, this does not work on AIX for prompt.

Get rid of those colors for ls and vi since blue on blue or red on red does not work well.

export PS1=”[\[email protected]\h:\${ORACLE_SID}]\${PWD}\n$ ”
export SQLPATH=/home/oracle/working/dinh
for DB in `cat /etc/oratab|grep -v \^#|grep -v \*|cut -d”:” -f1`
alias $DB=’export ORAENV_ASK=NO;export ORACLE_SID=’$DB’;. oraenv;sysresv’
alias osid=’ps -ef|grep [p]mon’
alias otab=’cat /etc/oratab|grep -v \#|grep -v \*|cut -d”:” -f1′
alias sysdba=’sqlplus “/ as sysdba”‘
alias oh=’cd $ORACLE_HOME’
alias dbs=’cd $ORACLE_HOME/dbs’

Think SQL Prompt breaks patching using OEM. Have not confirm 100%. But noticed the ones that failed have SQL Prompt defined.

select UPPER(SYS_CONTEXT(‘USERENV’,’SERVER_HOST’))||’:(&[email protected]&_CONNECT_IDENTIFIER)’||’> ‘ pr from dual;


Thank you for mentioning my whoami.sql script in your posting :-)


It’s really a great and helpful piece of information. I am glad that you shared this helpful info with us.
Please stay us up to date like this. Thanks for sharing.


Leave a Reply

Your email address will not be published. Required fields are marked *