Three DBA Scripts For Instant Answers: Who, What, How

Today’s post was written based on your emails, comments and feedback for last week’s post Mistakes Happen – How To Cancel Alter Database Datafile Resize Command.

I have received a tremendous amount of emails and requests to share the scripts mentioned last week! Wait no longer, you have the 3 scripts here today:

If you missed last week’s post, and are curious what was it all about, check in it out here.

Not only will I share the scripts with you, I will also give you some background, and explanation for each one.

Why are these scripts useful?

Because they give you the answers very quickly, and to the point. I encourage you to get in the habit of using these scripts, and get familiar with their content.

1. Who is connected to the database (with detailed information about each session): sessions.sql
2. What is the SQL Text of a specific SQL_ID: sqltext.sql “sql_id”
3. How to kill a session: pid.sql “session_id”

1. Who is connected to the database?

You might be using GUI tools to get this information, however it is very handy to have a script that gives you the answer within a second.

Why?

Because, you might not have access to a tool everytime you connect to a client.
Because, it will be faster.
Because, you can customize the script to show you the columns you want/need.

A good thing to remember is where is this script getting all the information from: gv$session.

Why do you need to know this? In case you don’t have access to the script, you still know how to get the information from the database.
You see, I have trouble remembering SQL Server dictionary views, and when I use SQL Server, I am stuck with the GUI.
With Oracle, I remember the views, and have no problem getting information from the database, even when my scripts are not available.

 
/* 
sessions.sql
Example: @sessions.sql
Copyright @2016 dbaparadise.com
*/     
set linesize 200
set pagesize 100
clear columns
col inst for 99999999
col sid for 9990
col serial# for 999990
col username for a12
col osuser for a16
col program for a10 trunc
col Locked for a6
col status for a1 trunc print
col "hh:mm:ss" for a8
col SQL_ID for a15
col seq# for 99990
col event heading 'Current/LastEvent' for a25 trunc
col state head 'State (sec)' for a14
 
select inst_id inst,  sid , serial# , username, 
 ltrim(substr(osuser, greatest(instr(osuser, '\', -1, 1)+1,length(osuser)-14))) osuser,
 substr(program,instr(program,'/',-1)+1,
 decode(instr(program,'@'),0,decode(instr(program,'.'),0,length(program),instr(program,'.')-1),instr(program,'@')-1)) program,  decode(lockwait,NULL,' ','L') locked, status, 
to_char(to_date(mod(last_call_et,86400), 'sssss'), 'hh24:mi:ss') "hh:mm:ss",
 SQL_ID, seq# , event, 
decode(state,'WAITING','WAITING '||lpad(to_char(mod(SECONDS_IN_WAIT,86400),'99990'),6),
'WAITED SHORT TIME','ON CPU','WAITED KNOWN TIME','ON CPU',state) state,substr(module,1,25) module, substr(action,1,20) action
from GV$SESSION 
where type = 'USER'
and audsid != 0    -- to exclude internal processess
order by inst_id, status, last_call_et desc, sid
/

2. What is the SQL Text of a specific SQL_ID?

You know the SQL_ID of a specific statement, and you want to find out the text of that statement, or you want confirmation of the actual text.

This is when sqltext.sql comes handy. It returns the text of the SQL_ID that you pass.

Remember: sqltext.sql is querying gv$sqltext

 
/* 
sqltext.sql
Example @sqltext.sql 6rv5za4tfnjs8
Copyright dbaparadise.com
*/
 
set define '&'
set verify off
define sqlid=&1
 
col sql_text for a80 word_wrapped
col inst_id for 9
break on inst_id
set linesize 150
 
select inst_id, sql_text 
from gv$sqltext 
where sql_id = '&sqlid'
order by inst_id,piece
/

3. Generate kill statement for a specific SID.

I love this script, because it is so convenient.

If you know you need to kill session 309, pid.sql will generate the kill commands for you, when you pass the SID as a parameter.
All you need to do, is copy paste the kill command and you are done!

If pid.sql is returning more than one row, you need to identify the exact process you want to kill. (There could be multiple sessions with the same SID)

 
/* 
pid.sql
Example @pid.sql 309
Copyright dbaparadise.com
*/
 
 
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
COLUMN Kill_Session_Command for A60
set linesize 200
 
Set define '&'
 
define _sid=&1
 
SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program,
       s.sql_id
FROM   gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND'
AND    s.sid=&_sid;
 
select 'alter system kill session ' || '''' || s.sid|| ',' || s.serial# || 
       ',@' || s.inst_id || ''';' as "Kill_Session_Command"
FROM   gv$session s
JOIN   gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND'
AND    s.sid=&&_sid;

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