get_sessions.sql


set echo off;
set pages 55
set termout on;
–set newpage 0;
set lines 199
set feedback off;
#
column sql_text format a60 word_wrapped
column box format a13
column username format a15
column os_user format a12
column program format a25
column pid format a6
column sid format a6
column ser# format a6
#
set heading off;
select ‘Sessions on database ‘||substr(name,1,8) from v$database;
set heading on;
#
alter session set nls_date_format=’HH24:MI DD-MON-YY’;
select substr(a.spid,1,9) pid
   , substr(b.sid,1,5) sid
   , substr(b.serial#,1,5) ser#
   , substr(b.machine,1,9) box
   , substr(b.username,1,10) username
   , substr(b.osuser,1,9) os_user
   , substr(b.program,1,25) program
   , b.logon_time logon_time
   , b.sql_id sql_id
   , b.seconds_in_wait idle_seconds
   , b.client_identifier
from v$session b, v$process a –, v$sqlarea c
where b.paddr = a.addr
   and b.sql_id is not null
   and type=’USER’
   and b.username not in (‘IGNITE’,’DBSNMP’)
order by idle_seconds;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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