Oracle: V$SESSION

Search for session by operating system user and/or program name

select /*+ ordered */
-- ses.sid,
-- ses.saddr,
-- ses.serial#,
   ses.program,
-- ses.osuser,
   ses.event,
   round(ses.wait_time_micro/1000/1000, 2) wait_s,   
-- ses.sql_id,
-- ses.prev_sql_id,
   ses.logon_time,
   substr(sql.sql_text, 1, 50)             cur_sql_text,
   substr(sqp.sql_text, 1, 50)             prev_sql_text
from
   v$session  ses                                    left join
   v$sqlarea  sql on ses.sql_id      = sql.sql_id    left join
   v$sqlarea  sqp on ses.prev_sql_id = sqp.sql_id
where
  osuser   = 'rene'          and
--program  = 'SQL Developer' and
  program  = 'sqlplus.exe';

Blocking sessions

--
-- See also v$session_blockers
--
select 
  ses.sid                      blocked_sid,
  ses.username                 blocked_username,
  ses.osuser                   blocked_osuser,
  ses.blocking_session_status  blocked_session_status, -- VALID:There is a blocking session
  --
  bls.sid                      blocking_sid,
  bls.username                 blocking_username,
  bls.osuser                   blocking_osuser,
  --
  ses.event                    blocked_event,
  bls.event                    blocking_event,
  --
  ses.sql_id                   blocked_sql_id,
  bls.sql_id                   blocking_sql_Id
from
  gv$session ses                                           join
  gv$session bls on ses.blocking_session  = bls.sid  and
                    ses.blocking_instance = bls.inst_id;

audsid

Columns MODULE, ACTION, CLIENT_INFO and CLIENT_IDENTIFIER

begin

  dbms_application_info.set_module (
    module_name => 'tq84_module',
    action_name => 'tq84_action'
  );

end;
/

select
  sid,
  to_number(sys_context('userenv', 'sid')) sid_verify,
  action
from
  v$session
where
  module = 'tq84_module';

module_hash

select
  module, 
  module_hash,
  ora_hash(module)
from
  v$session
where
  module_hash != ora_hash(module);

sql_exec_id

Joins

select /*+ ordered */
  ses.sid,
  ses.logon_time   ses_logontime,
  prc.pid          prc_pid,        -- Oracle process identifier
--prc.sosid        prc_sosid,      -- OS process/thread identifier
  prc.spid         prc_spid,       -- OS process identifier
--prc.stid         prc_stid,       -- OS thread identifier
--ses.paddr        prc_addr,
--ses.taddr        trx_addr,
  trx.start_time   trx_start_time,
  case when pxs.sid is not null then
    case
      when ses.sid = pxs.qcsid then 'Query coordinator'
      else 'Query server, QC is ' || pxs.qcsid
    end
  end  parallel_info,
  sqc.command_name,
--aud.name         aud_name,
  sql.sql_text     sql_text
from
  v$session     ses                                                   join
  v$process     prc on ses.paddr           = prc.addr            left join
  v$transaction trx on ses.taddr           = trx.addr            left join
  v$sql         sql on ses.sql_address     = sql.address      and  
                       ses.sql_hash_value  = sql.hash_value      left join
  v$sqlcommand  sqc on ses.command         = sqc.command_type    left join
  v$px_session  pxs on ses.sid             = pxs.sid          and
                       ses.serial#         = pxs.serial#         left join
  audit_actions aud on ses.command         = aud.action
order by
  ses.sid
;

select
   count(ses.sid   ) cnt_ses,
   count(sql.sql_id) cnt_sql
from
   v$session ses left join v$sql sql on ses.sql_id         = sql.sql_id     and ses.sql_child_number = sql.child_number
-- v$session ses left join v$sql sql on ses.sql_address    = sql.address    and ses.sql_child_number = sql.child_number
-- v$session ses left join v$sql sql on ses.sql_hash_value = sql.hash_value and ses.sql_child_number = sql.child_number
;

sid

sid, serial

type

Waiting sessions

SADDR

Columns

TODO

select
  count(         *      ),
  count(distinct saddr  ),
  count(distinct sid    ),
  count(distinct serial#),
  count(distinct paddr  ),
  count(         taddr  ),
  count(distinct taddr  )
from
  v$session;

alter session disable parallel ddl;
alter session disable parallel dml;
alter session disable parallel query;



select
  pdml_enabled,
  pdml_status,
  pddl_status,
  pq_status
from
  v$session where sid = sys_context('userenv', 'sid');
--
-- PDM PDML_STA PDDL_STA PQ_STATU
-- --- -------- -------- --------
-- NO  DISABLED DISABLED DISABLED



alter session enable parallel query;


select
  pdml_enabled,
  pdml_status,
  pddl_status,
  pq_status
from
  v$session where sid = sys_context('userenv', 'sid');
-- PDM PDML_STA PDDL_STA PQ_STATU
-- --- -------- -------- --------
-- NO  DISABLED DISABLED ENABLED




alter session enable parallel ddl;

select
  pdml_enabled,
  pdml_status,
  pddl_status,
  pq_status
from
  v$session where sid = sys_context('userenv', 'sid');
--
-- PDM PDML_STA PDDL_STA PQ_STATU
-- --- -------- -------- --------
-- NO  DISABLED ENABLED  ENABLED




alter session enable parallel dml;

select
  pdml_enabled,
  pdml_status,
  pddl_status,
  pq_status
from
  v$session where sid = sys_context('userenv', 'sid');
--
-- PDM PDML_STA PDDL_STA PQ_STATU
-- --- -------- -------- --------
-- YES ENABLED  ENABLED  ENABLED

select
 --count(*),
  program 
from
  v$session
where
  program not like 'oracle@%'
group by
  program
order by
  count(*) desc;
-- The query might find programs such as:
--   tabprotosrv.exe
--   Toad.exe
--   JDBC Thin Client
--   SQL Developer
--   EXCEL.EXE
--   MSACCESS.EXE
--   WIReportServer.exe
--   plsqldev.exe
--   WebIRichClient.exe
--   OMS
--   sqlplus.exe
--   DTExec.exe
--   Rgui.exe
--   APEX Listener
--   brioqry.exe  

See also