Oracle Query sql_text in sql area,session,process,pid,cpu time,users

** Myself
#Query for active users with session,process,sql text  (example ID=2777)
select b.sql_text,p.spid
from v$session s, v$sqlarea b, v$process p
where s.sql_address=b.address
   and s.paddr=p.addr
   and p.spid=2777;
#For all active users with session,process,sql text 
select b.sql_text,p.spid
from v$session s, v$sqlarea b, v$process p
where s.sql_address=b.address
   and s.paddr=p.addr;
#CPU Time and OS process
COLUMN sid                     FORMAT 999            HEADING 'SID'
COLUMN oracle_username         FORMAT a12            HEADING 'DB User'     JUSTIFY right
COLUMN os_username             FORMAT a12            HEADING 'OS User'        JUSTIFY right
COLUMN session_program         FORMAT a18            HEADING 'Session Program'
COLUMN session_machine         FORMAT a8             HEADING 'Machine'         JUSTIFY right TRUNC
COLUMN session_cpu           FORMAT 9,999,999,999  HEADING 'CPU(ms/s)'
COLUMN sql_text  FORMAT a80 HEADING 'SQL COMMAND'        JUSTIFY center
SET pagesize 50
SELECT
       s.sid sid
       ,lpad(s.username,12)  oracle_username
      , lpad(s.osuser,9)     os_username
      , s.program            session_program
      , lpad(s.machine,8)    session_machine
       ,(select ss.value/1000 from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and
           sn.statistic# = ss.statistic# and
           sn.name = 'CPU used by this session') session_cpu
       ,sql_text
FROM
       v$sqlarea sqlarea, v$session s
WHERE
        s.sql_address    = sqlarea.address
ORDER BY session_cpu desc
;
#Show all connected users
set lines 100 pages 999
col ID format a15
select username
,      sid || ',' || serial# "ID"
,      status
,      last_call_et "Last Activity"
from   v$session
where  username is not null
order by status desc
,        last_call_et desc
/


#Time since last user activity
set lines 100 pages 999
select username
,      floor(last_call_et / 60) "Minutes"
,      status
from   v$session
where  username is not null
order by last_call_et
/
#Sessions sorted by logon time
set lines 100 pages 999
col ID        format a15
col osuser    format a15
col login_time    format a14
select     username
,    osuser
,    sid || ',' || serial# "ID"
,    status
,    to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
,    last_call_et
from    v$session
where    username is not null
order    by login_time
/
#Show user info including os pid
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
order     by to_number(p.spid)
/
#Show a users current sql
select sql_text
from   v$sqlarea
where  (address, hash_value) in
(select sql_address, sql_hash_value
        from v$session
        where username like '&username')
/
#Session status associated with the specified os process id
select    s.username
,    s.sid
,    s.serial#
,    p.spid
,    last_call_et
,    status
from     V$SESSION s
,    V$PROCESS p
where    s.PADDR = p.ADDR
and    p.spid='&pid'
/

#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 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;
/
#Display any long operations
set lines 100 pages 999
col username format a15
col message format a40
col remaining format 9999
select    username
,    to_char(start_time, 'hh24:mi:ss dd/mm/yy') started
,    time_remaining remaining
,    message
from    v$session_longops
where    time_remaining = 0
order by time_remaining desc
/
#List open cursors per user
set pages 999
select     sess.username
,    sess.sid
,    sess.serial#
,    stat.value cursors
from    v$sesstat stat
,    v$statname sn
,    v$session sess
where    sess.username is not null
and    sess.sid = stat.sid
and    stat.statistic# = sn.statistic#
and    sn.name = 'opened cursors current'
order by value
/
#Or alternatively...
set lines 100 pages 999
select count(hash_value) cursors
,      sid
,      user_name
from   v$open_cursor
group by
       sid
,      user_name
order by
       cursors
/