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 = …

Display Oracle v$session_longops sessions" SQL Code 1set lines 100 pages 999 2col username format a15 3col message format a40 4col remaining format 9999 5select username 6, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started 7, time_remaining remaining 8, message 9from v$session_longops 10where …

List 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 = …

Display 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 …

Display 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 …

Select Oracle user info including os pid SQL Code 1col "SID/SERIAL" format a10 2col username format a20 3col osuser format a15 4col program format a50 5select s.sid || ',' || s.serial# "SID/SERIAL" 6, s.username 7, s.osuser 8, p.spid "OS PID" 9, s.program 10from v$session s 11, v$process …

Oracle sessions sorted by logon time 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 OSUSER ID STATUS LOGIN_TIME LAST_CALL_ET 2-------------------- …

Display 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-------------------- ---------- …

Show 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, …