Difference between revisions of "Database sessions"
From dbawiki
(→Session status associated with the specified os process id) |
|||
| Line 30: | Line 30: | ||
and username like '&username' | and username like '&username' | ||
); | ); | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
===All active sql=== | ===All active sql=== | ||
set feedback off | set feedback off | ||
Revision as of 19:48, 10 November 2012
Sessions sorted by login time
set lines 100 set pages 999 col ID format a15 col osuser format a15 col login_time format a14 select s.username , s.osuser , s.sid || ',' || s.serial# "ID" , p.spid "OS PID" , s.status , to_char(s.logon_time, 'dd-mon-yy hh24:mi') login_time , s.last_call_et from v$session s , v$process p where 1=1 and s.paddr = p.addr and username is not null order by login_time;
Show a users current sql
select sql_text
from v$sqlarea
where 1=1
and (address, hash_value) in
(
select sql_address
, sql_hash_value
from v$session
where 1=1
and username like '&username'
);
All active sql
set feedback off
set serveroutput on size 9999
column username format a20
column sql_text format a55 word_wrapped
begin
for x in
(
select username||'('||sid||','||serial#||') ospid = '|| process ||' program = ' || program username
, to_char(LOGON_TIME,' Day HH24:MI') logon_time
, to_char(sysdate,' Day HH24:MI') current_time
, sql_address
, sql_hash_value
from v$session
where 1=1
and status = 'ACTIVE'
and rawtohex(sql_address) != '00'
and username is not null
) loop
for y in (select sql_text
from v$sqlarea
where address = x.sql_address )
loop
if ( y.sql_text not like '%listener.get_cmd%' and y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end; /
