Problem solve Get help with specific problems with your technologies, process and projects.

What SQL is running and who is running it?

A script to determine what SQL is running, who is running it, and other information.

Many times you may need to find what SQL is currently running and which user is running it. The following SQL runs on Oracle versions 7 and 8.1.7 and gives the SID, Serial#, SQL text, and the Oracle and OS user running it. The SID and serial number information may be used to kill the particular session if required. I have the script saved as whatdousers.sql and just type @whatdousers at the SQL prompt. Here is the script:

REM NAME : whatdousers.sql 
REM 
REM DESCRIPTION: This script runs at the SQL prompt and gives the text 
REM of the SQL being currently run on the machine. Also gives the SID 
REM and Serial# which may be used to killl the session by using the 
REM following at the SQL prompt. 
REM
REM ALTER SYSTEM KILL SESSION 'SID,SERIAL#' 
REM 
REM The script starts here:

break on sid skip 1 on username 
column sid format 999 
column username format a10 
select a.sid,a.serial#,a.username,b.sql_text 
from v$session a,v$sqltext b 
where a.username is not null 
and a.status = 'ACTIVE' 
and a.sql_address = b.address 
order by 1,2,b.piece; 
REM End of script 

Reader Feedback

Ofer H. writes: One problem with this script is that many times the running session is an operation system script. In this situation, you need to kill the session also on the Unix side. To do so, you need to know the operation system session id. That information is given by the audsid column in the v$session. So I use a better script to find out who is running SQL on the database. This script shows only the beginning of the SQL text, and it's useful for the monitoring process.

select decode(sum(decode(s.serial#,l.serial#,1,0)),0,'No','Yes') " ",
          s.sid "Session ID",s.status "Status",
          s.username "Username", RTRIM(s.osuser) "OS User",
          b.spid "OS Process ID",s.machine "Machine Name",
          s.program  "Program",c.sql_text "SQL text"
   from v$session s, v$session_longops l,v$process b,
        (select address,sql_text from v$sqltext where piece=0) c
 where (s.sid = l.sid(+)) and s.paddr=b.addr and s.sql_address = c.address
 group by s.sid,s.status,s.username,s.osuser,s.machine,
          s.program,b.spid, b.pid, c.sql_text order by s.sid
/

Nick J. writes, in response to Ofer's comments: A question about this line of code:

decode(sum(decode(s.serial#,l.serial#,1,0)),0,'No','Yes')
This line returns YES in a 3-tier server even if the session is not active. Hence it may be better to check if time_remaining > 0. I'd like to hear your opinion about this.

Ofer responds: Well, time_remaining is just an estimate for the current action. It says nothing about the remainder of the next actions in the current session. Also the current action can be short, but prior long actions makes the whole session kind of long. There is an option to change the query so inactive sessions will be always NO:

decode(sum(decode(decode(s.status,"ACTIVE",s.serial#,0),l.serial#,1,0)),0,'No','Yes') " "...

I thought that information of long transactions of a temporary inactive sessions is still interesting me. Also I made a little change to this query. If one wants to see most of the SQL statement, he can replace querying of v$sqlarea by v$sqltext:

select
decode(sum(decode(decode(s.status,"ACTIVE",s.serial#,0),l.serial#,1,0)),0,'No','Yes') " ",
       s.sid "Session ID",s.status "Status",
       s.username "Username", RTRIM(s.osuser) "OS User",
       b.spid "OS Process ID",s.machine "Machine Name",
       s.program  "Program",c.sql_text "SQL text"
  from v$session s, v$session_longops l,v$process b,
       v$sqlarea c
 where (s.sid = l.sid(+)) and s.paddr=b.addr and s.sql_address = c.address
 group by s.sid,s.status,s.username,s.osuser,s.machine,
          s.program,b.spid, b.pid, c.sql_text order by s.sid
/

For More Information

  • What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close