Show all Active SQL for Sessions using v$session and v$sqlarea SQL Code` 1set feedback off 2set serveroutput on size 9999 3column username format a20 4column sql_text format a55 word_wrapped 5begin 6 for x in 7 (select username||'('||sid||','||serial#||') ospid = '|| process || 8 ' program = …
Read MoreList all open oracle cursors by user or username SQL Code 1set pages 999 lines 300 2col username format a40 3select sess.username as username 4, sess.sid as sid 5, sess.serial# as serial 6, stat.value cursors 7from v$sesstat stat 8, v$statname sn 9, v$session sess 10where sess.username is not null 11and sess.sid = …
Read MoreDisplay Session status associated with the specified os process id SQL Code 1select s.username 2, s.sid 3, s.serial# 4, p.spid 5, last_call_et 6, status 7from V$SESSION s 8, V$PROCESS p 9where s.PADDR = p.ADDR 10and p.spid='&pid' 11/ Sample Oracle Output: 1Enter value for pid: 9999 2old 10: and …
Read MoreDisplay the users current Session SQL SQL Code 1Select sql_text 2from v$sqlarea 3where (address, hash_value) in 4(select sql_address, sql_hash_value 5 from v$session 6 where username like '&username') 7/ Sample Oracle Output: 1Enter value for username: sys 2old 6: where username like …
Read MoreDisplay all Oracle users by the time since last user activity SQL Code 1set lines 100 pages 999 2select username 3, floor(last_call_et / 60) "Minutes" 4, status 5from v$session 6where username is not null 7order by last_call_et 8/ Sample Oracle Output: 1USERNAME Minutes STATUS 2-------------------- ---------- …
Read MoreShow all the Oracle users connected to the database SQL Code 1set lines 100 pages 999 2col ID format a15 3col USERNAME format a20 4select username 5, sid || ',' || serial# "ID" 6, status 7, last_call_et "Last Activity" 8from v$session 9where username is not null 10order by status desc 11, …
Read More