
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:
- sessions.sql
- sqltext.sql
- pid.sql
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