Login.sql – The Answer To DBA Questions Where, And Who Am I?

Published: Thu, 09/01/16

Image

You are in the middle of a change deployment into pre-production database. You need to run a few scripts. There is no pressure, this is pre-prod. You get interrupted after running the first script. You deal with the problem, and return to your initial task of running the second script from the deployment plan.


You open up sqlplus, drag and drop script number two and notice the feedback:


Table dropped.


Table dropped.


Table dropped.


At this moment you get a strange feeling, and your inner voice tells you


“Did you run this in the pre-prod environment, or was that production.”


The first emotion you experience is panic, then fear, then you can see in front of your eyes within fractions of seconds, all the work you need to do, to restore the 3 tables.


The blood rushes out of your brain, face, hands and finally your body.


You hesitate for a moment to check the database name, because you do not want to face the truth:


You Just Dropped Three Tables In Production.


Finally you get the courage and run a select statement to get the database name, and notice that actually it was test, and not prod.


Relief.


Did this happen to you before? Maybe not dropping a table in production. Maybe it was a shutdown of the wrong database? Or killing a session in the wrong database?

Do you want to go through this panic again?


I don’t.


DBAs are humans. DBAs make mistakes too.


The question is: How can we prevent these mistakes from happening?


Conventional wisdom tells us:

  • be extra careful when running a script.
  • verify database name, prior to running a script.
  • verify username prior to running a script.
  • focus on the task at hand, don’t allow interruption (yeah right, I would need an isolating bubble in my cubicle for this to happen)

These are all great suggestions, however do you see a problem with them?


All of these suggestions rely on one person, which is YOU. You can forget to check the database name. Maybe you already checked, at the beginning of deployment, and when you got back to running the scripts, you accidentally clicked on the other sqlplus window.


I don’t know about you, but I have about 10 putty sessions and 5 sqlplus sessions open at any given time. It is easy to click on the wrong window.


How can we prevent these mistakes from happening?


AUTOMATE.


Take yourself out of the equation.


The environment where you are connected to, needs to display the information about itself.


1.What Information Does A DBA Need To Know When Connected To A Database?
2.What Controls SQLPLUS Settings?
3.How Do I Setup SQLPLUS, To Show The Information I Want/Need?
4.Bonus: whoami.sql – Script To Run Anytime


1.What Information Does A DBA Need To Know When Connected To A Database?

When I connect to a database, at a minimum I need to know WHERE I am, and WHO I am:

  • database name
  • username

It is also nice to have the current time displayed. Why?

  • Because when you deploy changes, or run audit scripts, you don’t need to turn time on anymore.
    How many times did you run a script, forgot to turn time on and ended up running the script again? I know it used to happen to me all the time. Not anymore.


It is also nice to have the server name and instance number, if you are running RAC. Why?

  • Because it is good to know if you are on instance 1 or 2 or 3.
    I can hear you say, “You can find this information quickly by querying a V$ view”.


    And you are correct. And I like to automate as much as possible.

It is also nice to have the session identifier for your connection, your SID. Why?

  • Because if I need to identify this session, I can do it very quickly.

2.What Controls SQLPLUS Settings?

There are 2 files that control sqlplus settings:

  • glogin.sql in $ORACLE_HOME/sqlplus/admin
  • login.sql in the SQLPATH environment variable


When you start sqlplus, the tool looks for one of these files. If and when these files are found, sqlplus will read and execute them. The login.sql file overwrites glogin.sql

My preference is to use login.sql file.


3.How Do I Setup SQLPLUS, To Show The Information I Want/Need?

There are a few ways to do this, depending on where you are running sqlplus.

I usually run sqlplus on the Unix server where the databases are, and also on my Windows desktop.

On Unix, the server name is already displayed at the top of your putty session, no need to worry about that.


I setup the login.sql file to display time, db name and username. You can set other preferences in the file, such as linesize, pagesize and specific column sizes.


On Unix define $SQLPATH variable, and save login.sql to that location.


login.sql
 
set linesize 180
set pagesize 100
set time on
column dbname noprint new_value _dbname 
column user noprint new_value _dbuser 
 
select global_name  name from global_name;
select user from dual;
set sqlprompt "&_dbname. (&_dbuser) >"
 
/* 
Sample output:
 
22:04:21 HRTST.WORLD (DROBETE) >
*/

On Windows, this is how I like my SQLPLUS command window. Notice at the top, the database name, instance name, SID and hostname.


 
Image

To set this up, you need to set %SQLPATH% variable, and save login.sql script to that location.


login.sql
 
set define '&'
set time on
 
def _i_conn="&_connect_identifier"
 
col i_user noprint  new_value _i_user
col i_host_name  noprint for a30 new_value _i_host
col i_instance_name  noprint for a12 new_value _i_inst
 
select lower(user) as i_user, lower(instance_name) as i_instance_name, 
lower(host_name) i_host_name
from v$instance
/
 
column sid noprint new_value mysid
 
select to_char((sid)) sid from v$mystat where rownum=1
/
 
host title &_i_user@&_i_conn [sid=&mysid inst=&_i_inst host=&_i_host]
set sqlprompt "&_i_conn (&_i_user) >"


Tanel Poder has a great example of his script called i.sql, with multiple other options to setup. You can check it out here: –http://blog.tanelpoder.com/files/scripts/i.sql


4.Bonus: whoami.sql – Script To Run Anytime

If you are connected to a database at a client, where you have no control over glogin.sql or login.sql, then you need to manually verify and confirm the environment you are in.


I came across recently an older blog post by Uwe Hesse, where he shared with his audience the whoami script.


I thought the name of the script is such a great choice. I am sharing with you Uwe’s script below. Here is the link to the original post: Oracle Database whoami.


Anytime you are in doubt where you are and who you are (in the database) just ask the question @whoami, and you shall find out.



 
set serveroutput on
begin
dbms_output.put_line('USER: '||sys_context('userenv','session_user'));
dbms_output.put_line('SESSION ID: '||sys_context('userenv','sid'));
dbms_output.put_line('CURRENT_SCHEMA: '||sys_context('userenv','current_schema'));
dbms_output.put_line('INSTANCE NAME: '||sys_context('userenv','instance_name'));
dbms_output.put_line('DATABASE ROLE: '||sys_context('userenv','database_role'));
dbms_output.put_line('OS USER: '||sys_context('userenv','os_user'));
dbms_output.put_line('CLIENT IP ADDRESS: '||sys_context('userenv','ip_address'));
dbms_output.put_line('SERVER HOSTNAME: '||sys_context('userenv','server_host'));
dbms_output.put_line('CLIENT HOSTNAME: '||sys_context('userenv','host'));
end;
/


Now you are equipped to run scripts in the proper environment, and there are no excuses to make mistakes.

Go ahead and setup your login.sql the proper way!


Remember - sharing is caring...

Forward this email to a friend!

 You're the best!
 
 
 
Diana Robete
Sincerely,
Diana Robete
diana@dbaparadise.com