DBA Paradise

The place where DBAs grow

  • Home
  • Database
    • Oracle
    • SQL Server
    • Certification
  • My Courses
  • Personal Growth
  • About
  • Contact
Login.sql – The Answer To DBA Questions Where, And Who Am I?

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

August 31, 2016 Off By dianarobete

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.

?View Code ENGLISH
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.

login.sql setup

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

?View Code ENGLISH
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.

?View Code ENGLISH
 
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!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana


CategoryDatabase Oracle
Tagsdatabase name dba connection host name login.sql oracle database sid

5 Comments

  • Andrea says:
    September 1, 2016 at 7:50 am

    Hello Diana.

    Good post. This is the one I use

    REM Disabling the output
    set termout off
    define gname=idle
    column global_name new_value gname

    select REGEXP_SUBSTR(SYS_CONTEXT(‘USERENV’,’HOST’),'(^[a-zA-Z0-9]+)’)||’.’||SYS_CONTEXT(‘USERENV’,’INSTANCE_NAME’)||'(‘|| user||’)’ global_name from dual;
    set sqlprompt ‘&gname> ‘

    REM Enable the output
    define _editor=vi
    set long 99999
    set serveroutput on
    alter session set nls_date_format=’dd/mm/yyyy hh24:mi:ss’;
    set termout on

    • dianarobete says:
      September 1, 2016 at 8:25 pm

      Thanks Andrea! That is a good one as well! Thank you for sharing!

  • Adinath Kamode says:
    October 27, 2016 at 1:58 am

    Thanks Diana,
    I modified glogin.sql to spool SQL commands and their output to file as they go on. It helped me in many situation to analyze command outputs.
    — My code is given below. It will also set prompt to connected instance and user.

    — === Custom code ===
    set serveroutput on size 100000
    set trimspool on
    set long 5000
    set linesize 800
    set pagesize 100
    set pause off
    set termout off
    define gname=idle
    column global_name new_value gname
    column stime new_value sptime
    select upper(user)||’@’||upper(NAME) global_name,
    ‘C:\Adinath\Work\SQLLog\’||upper(user)||’_’||upper(NAME)||’_’||to_char(sysdate,’FMDD_MON_RR_HH12_MI_PM’)||’.txt’ stime
    from v$database;
    set sqlprompt ‘&gname> ‘
    set termout on
    COLUMN INSTANCE_NAME FORMAT A15 HEADING “Instance Name”
    COLUMN HOST_NAME FORMAT A15 HEADING “Server Name”
    COLUMN STARTUP_TIME FORMAT A30 HEADING “Startup Time”
    COLUMN STATUS FORMAT A10 HEADING “Status”
    COLUMN LOGINS FORMAT A15 HEADING “Login Allowed ?”
    COLUMN UPDAYS HEADING “Days up”
    COLUMN VERSION HEADING “Version”
    COLUMN PLATFORM_NAME FORMAT A20 HEADING “Operat. System”
    COLUMN OPEN_MODE FORMAT A15 HEADING “Open Mode”
    COLUMN LOG_MODE FORMAT A15 HEADING “Archive Mode”
    COLUMN CREATED HEADING “Created On”

    SELECT I.INSTANCE_NAME,HOST_NAME,D.PLATFORM_NAME,VERSION,D.OPEN_MODE,D.LOG_MODE,
    STATUS,LOGINS,TO_CHAR(CREATED,’FMDD-MONTH-RRRR’) CREATED,
    TO_CHAR(STARTUP_TIME,’FMDD-MONTH-RRRR HH24:MI’) STARTUP_TIME,TRUNC(SYSDATE)-TRUNC(STARTUP_TIME) UPDAYS
    FROM V$INSTANCE I,V$DATABASE D
    WHERE I.INSTANCE_NAME = D.NAME;
    set pause on
    set pause “”
    prompt Spool file is &sptime
    spool ‘&sptime’

    Thanks,
    Adinath

    • dianarobete says:
      October 27, 2016 at 9:46 am

      Thank you Adinath for your input!

  • How To Make login.sql Work Again In 12.2 and 11.2.0.4 – DBA Paradise says:
    August 30, 2017 at 10:37 pm

    […] summer, I published a post, Login.sql – The Answer To DBA Questions Where, And Who Am I?, which basically taught you how to setup the prompt in SQLPlus, in order to know exactly WHO you […]

Comments are closed.

Recent Posts

  • What Are Single Row Functions
  • READ vs SELECT Privilege
  • What Is Deferred Segment Creation?

Recent posts

What Are Single Row Functions

What Are Single Row Functions

June 20, 2022 Off
READ vs SELECT Privilege

READ vs SELECT Privilege

April 14, 2022 Off
What Is Deferred Segment Creation?

What Is Deferred Segment Creation?

April 11, 2022 Off
What Is The Difference Between Oracle Table And Oracle Segment?

What Is The Difference Between Oracle Table And Oracle Segment?

March 17, 2022 Off
How To Get Historical Row Count In a Table?

How To Get Historical Row Count In a Table?

March 12, 2022 Off
What Is Oracle’s Identity Column?

What Is Oracle’s Identity Column?

March 1, 2022 Off
How Can NLS_DATE_FORMAT Help You?

How Can NLS_DATE_FORMAT Help You?

January 12, 2022 Off
What Is Oracle Instance Recovery?

What Is Oracle Instance Recovery?

January 6, 2022 Off

Recent Posts

  • What Are Single Row Functions
  • READ vs SELECT Privilege
  • What Is Deferred Segment Creation?

Lets Get Technical

Terms Of Service
Privacy Policy
Contact Us

Credits

Website Logo: Travel And Tourism graphic by Freepik from Flaticon is licensed under CC BY 3.0. Made with Logo Maker

Proudly powered by WordPress | Theme: Balanced Blog