Oracle Database Session queries

DBA Blockers displays HOLDING sessions

SELECT * FROM dba_blockers;

DBA Waiters displays WAITING_SESSION, HOLDING_SESSION and LOCK details

SELECT * FROM DBA_WAITERS;
select
    sid, serial#,USERNAME, STATUS,SCHEMANAME,TERMINAL,PROGRAM,BLOCKING_SESSION, sql_id
from v$session
where sid in (select distinct BLOCKING_SESSION
from v$session where BLOCKING_SESSION IS nOT NULL);
select count(*), HOLDING_SESSION
from dba_waiters
where HOLDING_SESSION in (SELECT HOLDING_SESSION from dba_blockers)
group by HOLDING_SESSION;

Identify the session details and SQL ID

set lines 200
col USERNAME for a30
col STATUS for a10
col SCHEMANAME for a15
col TERMINAL for a10
col PROGRAM for a25
col SQL_ID for a15
select
    sid, serial#,USERNAME, STATUS,SCHEMANAME,TERMINAL,PROGRAM,BLOCKING_SESSION, sql_id
from v$session where sid=&sid;

What’s the SQL statement

set long 3000
select SQL_TEXT from v$sql where sql_id='&sql_id';

To identify the OS PID and Session ID, Serial#, use the following

set long 2000;
select sql_text from v$sql where sql_id='&SQL_ID';

col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select	s.sid || ',' || s.serial# "SID/SERIAL"
,	s.username
,	s.osuser
,	p.spid "OS PID"
,	s.program
from	v$session s
,	v$process p
Where	s.paddr = p.addr
and s.sid in (<from dba_blockers or HOLDING_SESSION from dba_waiters>)
order 	by to_number(p.spid)
/

To get necessary information straight from the view v$session_longops.

col OPNAME for a20
col TARGET for a30
col MESSAGE for a40
col UNITS for a20
select * from (
  select opname, target, sofar, totalwork,
         units, elapsed_seconds, message
  from v$session_longops order by start_time desc)
where rownum <=1;

On a busy server one would like to add filter on sid and serial# in the inner query to get the info only about the necessary session and the query is as follows:

col OPNAME for a20
col TARGET for a30
col MESSAGE for a40
col UNITS for a20
select * from (
  select opname, target, sofar, totalwork,
         units, elapsed_seconds, message
  from v$session_longops
  where sid =&SID and serial# =&SERIAL_NO
  order by start_time desc)
where rownum <=1;

Long running sessions

set lines 200
col username for a25;
col curr for a10
col logon for a10
SELECT username,sid, serial#,
           TO_CHAR(CURRENT_TIMESTAMP,'HH24:MI:SS') AS curr,
           TO_CHAR(start_time,'HH24:MI:SS') AS logon,
           (sysdate - start_time)*24*60 AS mins
    FROM V$SESSION_LONGOPS
    WHERE    username is not NULL
      AND (SYSDATE - start_time)*24*60 > 1 ;
Advertisements

About mpoojari
Oracle Database/Applications Administrator

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: