Search notes:

Oracle: V$SESSION

Each record in v$session shows information about a «current» session.

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

v$sessions records the operating system user name and the program name with which he/she connects to Oracle in the columns username and program.
The Oracle user name (schema) with which a user connected to Oracle is recorded in username.
With this information, it is possible to find an «interesting» session:
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';
Github repository oracle-patterns, path: /Installed/dynamic-performance-views/session/find-my-interesting-sessions.sql

Blocking sessions

If a sesssion cannot continue with the current execution because it is blocked by another session, the blocked session records the instance and session id of the blocking session in the columns blocking_instance and blocking_session.
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;
See also v$session_blockers.

Columns

SADDR The session address (RAW)
SID Session ID (unique number for a given point in time). See also idnetification of sessions and identifying one's own session.
SERIAL# The combination of SERIAL# and SID is unique across time
AUDSID The Session ID of session that audits a session
PADDR Address of the process (RAW), can be used to join with v$process
USER# ID of the user who is connected to the session
USERNAME Name of the user
COMMAND Type of command (select command_name from v$sqlcommand where command_type = command)
OWNERID ID of the user who owns the migratable session.
TADDR Address (RAW) of the transaction, join with v$transaction.
LOCKWAIT Address of the lock that the session is waiting for, NULL if not waiting.
STATUS ACTIVE, INACTIVE, KILLED, CACHED or SNIPED. Compare with column STATE.
SERVER DEDICATED, SHARED, PSEUDO, POOLED or NONE
SCHEMA#
SCHEMANAME
OSUSER Name of operating system user who is connected to session
PROCESS Operating system process id (PID)
MACHINE
PORT
TERMINAL
PROGRAM
TYPE
SQL_ADDRESS, SQL_HASH_VALUE SQL_ADDRESS and SQL_HASH_VALUE identify the SQL statement that is currently executed by the session (join with v$sql)
SQL_ID
SQL_CHILD_NUMBER
SQL_EXEC_START
SQL_EXEC_ID
PREV_SQL_ADDR
PREV_HASH_VALUE
PREV_SQL_ID
PREV_CHILD_NUMBER
PREV_EXEC_START
PREV_EXEC_ID
PLSQL_ENTRY_OBJECT_ID
PLSQL_ENTRY_SUBPROGRAM_ID
PLSQL_OBJECT_ID
PLSQL_SUBPROGRAM_ID
MODULE
MODULE_HASH
ACTION
ACTION_HASH
CLIENT_INFO
FIXED_TABLE_SEQUENCE
ROW_WAIT_OBJ#
ROW_WAIT_FILE#
ROW_WAIT_BLOCK#
ROW_WAIT_ROW#
TOP_LEVEL_CALL#
LOGON_TIME
LAST_CALL_ET If status in ('ACTIVE', 'INACTIVE'), this value indicates the number of seconds since the status changed to active or inactive. ET likely stands for elapsed time. See also MOS note 2493379.1
PDML_ENABLED Replaced by PDML_STATUS
FAILOVER_TYPE NONE, SESSION, SELECT, TRANSACTION or AUTO
FAILOVER_METHOD
FAILED_OVER
RESOURCE_CONSUMER_GROUP
PDML_STATUS, ENABLED, DISABLED or FORCED. PDML = Parallel DML. Replaces PDML_ENABLED
PDDL_STATUS ENABLED, DISABLED or FORCED. PDDL = Parallel DDL
PQ_STATUS ENABLED, DISABLED or FORCED. PQ = Parallel Query.
CURRENT_QUEUE_DURATION
CLIENT_IDENTIFIER
BLOCKING_SESSION_STATUS
BLOCKING_INSTANCE
BLOCKING_SESSION
FINAL_BLOCKING_SESSION_STATUS
FINAL_BLOCKING_INSTANCE
FINAL_BLOCKING_SESSION
SEQ#
EVENT# Column value replicated from v$session_wait.
EVENT Column value replicated from v$session_wait.
P1TEXT Column value replicated from v$session_wait.
P1 Column value replicated from v$session_wait.
P1RAW Column value replicated from v$session_wait.
P2TEXT Column value replicated from v$session_wait.
P2 Column value replicated from v$session_wait.
P2RAW Column value replicated from v$session_wait.
P3TEXT Column value replicated from v$session_wait.
P3 Column value replicated from v$session_wait.
P3RAW Column value replicated from v$session_wait.
WAIT_CLASS_ID Column value replicated from v$session_wait.
WAIT_CLASS# Column value replicated from v$session_wait.
WAIT_CLASS Column value replicated from v$session_wait.
WAIT_TIME Column value replicated from v$session_wait.
SECONDS_IN_WAIT Column value replicated from v$session_wait.
STATE Column value replicated from v$session_wait. Compare with column STATUS.
WAIT_TIME_MICRO Column value replicated from v$session_wait.
TIME_REMAINING_MICRO Column value replicated from v$session_wait.
TOTAL_TIME_WAITED_MICRO
HEUR_TIME_WAITED_MICRO Column value replicated from v$session_wait.
TIME_SINCE_LAST_WAIT_MICRO Column value replicated from v$session_wait.
SERVICE_NAME
SQL_TRACE
SQL_TRACE_WAITS
SQL_TRACE_BINDS
SQL_TRACE_PLAN_STATS
SESSION_EDITION_ID
CREATOR_ADDR
CREATOR_SERIAL#
ECID
SQL_TRANSLATION_PROFILE_ID
PGA_TUNABLE_MEM The amount of tunable PGA memory, in bytes. v$process.pga_alloc_mem minus v$session.pga_tunable_mem is the amount of untunable memory.
SHARD_DDL_STATUS
CON_ID
EXTERNAL_NAME
PLSQL_DEBUGGER_CONNECTED
DRAIN_STATUS
DRAIN_DEADLINE

AUDSID

The column audsid can be joined with sys_context('userenv','SESSIONID') to find out which session is the "own one".
Alternatively, dbms_support.mysid can be used: where sid = sys.dbms_support.mysid.

MODULE, ACTION, CLIENT_INFO and CLIENT_IDENTIFIER

The values of the columns module, action and client_info can be set with procedures set_module, set_client_info and set_action of the dbms_application_info package
(See v$session_longops for an example).
The value of client_identifier can be set with dbms_session.set_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';
RMAN sets the value of client_info to something that can be queried with client_info like '%rman%'.

MODULE_HASH

It turns out that the value of module_hash is equal to ora_hash(module). Thus, the following select statement does not return a record:
select
  module, 
  module_hash,
  ora_hash(module)
from
  v$session
where
  module_hash != ora_hash(module);

SQL_EXEC_ID

As Tanel Poder points out, the value of sql_exec_id seems to start with 224 for every sql_id when Oracle is started.
sql_exec_id is also found in v$active_session_history and v$open_cursor.

SID

Join sid with v$sesstat to get some statistical information for a particular sesssion.

SID, SERIAL

sid and serial# can be used in a statement to to kill a session.

TYPE

The value of type can be used to identify background (type='BACKGROUND') and foreground (type='USER') processes.

SADDR

The value of saddr (the session address) might be joined to

STATUS, STATE

ACTIVE
INACTIVE
KILLED The session was killed while it was INACTIVE and the session's user did not yet see the ORA-00028 error message (MOS Note 107686.1)|
SNIPED
CACHED
TODO:
select
-- count(*),
   status,   
   state
from
   v$session
group by
   status,
   state
order by
   status;
--
-- ACTIVE     WAITED KNOWN TIME
-- ACTIVE     WAITED SHORT TIME
-- ACTIVE     WAITING
-- INACTIVE   WAITING


select
   count(*),
   event           -- Typically SQL*Net message from client ?
from
   v$session
where
   status = 'INACTIVE' and
   state  = 'WAITING'
group by
   event;

Joins

Some v$ views that can be joined to include:
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
;
Github repository oracle-patterns, path: /Installed/dynamic-performance-views/session/joins.sql
An attempt to find views that can be joined to v$session with a select statement is here.

Joining V$SESSION to V$SQL

There are (at least) three separate ways to join v$session to v$sql:
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
;

Waiting sessions

What a session is waiting for can be queried with v$session_wait

TODO

select
  count(         *      ),
  count(distinct saddr  ),
  count(distinct sid    ),
  count(distinct serial#),
  count(distinct paddr  ),
  count(         taddr  ),
  count(distinct taddr  )
from
  v$session;
Github repository oracle-patterns, path: /Installed/dynamic-performance-views/session/distinct_values.sql
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
Github repository oracle-patterns, path: /Installed/dynamic-performance-views/session/pdml-pddl-pq_status.sql

Some programs that might be seen connected to Oracle

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  

Big query

select
-- case when ses.sid = sys_context('USERENV', 'SID') then '*' else ' ' end myself,
   case when pxs.qcsid is not null then
        case when ses.sid     = pxs.qcsid     and
                  ses.serial# = pxs.qcserial#     then 'm'
                                                  else 's'
        end
   end                                                      pll, -- Parallel                                                                                       
   ses.sid,  ses.serial#,
-- User- and schema names ------------------------------------------------------------------------------------
-- ses.osuser,
   ses.username                                         ses_username, --- ses.schemaname ses_schemaname, -- ses.schema#,
   -------------------------------------------------------------------------------------
   sql.executions              sql_executions,
-- sql.sql_fulltext,
   -------------------------------------
   sql.rows_processed,
   round((sysdate - ses.sql_exec_start) * 60 * 60 * 24) sql_running_since_s,
   round(sql.elapsed_time / 1e6      , 2)               sql_accumulated_elapsed_time_s,
   round(sql.cpu_time     / 1e6      , 2)               sql_cpu_time_s,
   round(prc.cpu_used     / 1e6      , 2)               prc_cpu_used_s,
   --
   sql.sql_text,
   --
   mon.key                     sql_monitor_key,
   mon.binds_xml,
--'exec kill_my_session(' || ses.sid || ',' || ses.serial# || ');' kill_ses,


-- prc.background                          prc_background, -- >> 1 for a SYSTEM background process; NULL for foreground processes or non-SYSTEM background processes
-- Columns related to v$px_session --------------------------------------------------
   pxs.server_group,
   pxs.server_set,
   pxs.server#,
   pxs.degree, pxs.req_degree,
   pxs.con_id,
   pxs.qcsid, pxs.qcserial#,

-- Columns related to parallel execution -----------------------------------------------
 '!!!!!!!',
   ses.pddl_status,
   ses.pq_status,
   ses.pdml_status, -- pdml_status replaces ses.pdml_enabled,
   -------------------------------------------------------------------------------------

-- prc.serial#   prc_serial#,
-- ses.audsid,

   trx.status                                           trx_st,
   -----------------------------------------------------
   -- ses.seq# ,            -- >> A number that uniquely identifies the current or last wait (incremented for each wait)
   ses.event,               -- ses.event#,
   ses.wait_class,          -- ses.wait_class#, ses.wait_class_id,
   ses.state                                            ses_state,
   ses.status                                           ses_status,
   round(ses.wait_time_micro / 1e6, 4)                  ses_wait_time_s,
-- ses.time_remaining_micro,
   round(ses.time_since_last_wait_micro / 1000/1000, 4) ses_time_since_last_wait_s,
   ses.p1text,
   case when ses.p1text = 'driver id' then
     chr(to_number(substr(ses.p1raw, 1, 2), 'xx')) ||
     chr(to_number(substr(ses.p1raw, 3, 2), 'xx')) ||
     chr(to_number(substr(ses.p1raw, 5, 2), 'xx')) ||
     chr(to_number(substr(ses.p1raw, 7, 2), 'xx')) ||
     chr(to_number(substr(ses.p1raw, 9, 2), 'xx')) ||
     chr(to_number(substr(ses.p1raw,11, 2), 'xx')) ||
     chr(to_number(substr(ses.p1raw,13, 2), 'xx')) ||
     chr(to_number(substr(ses.p1raw,15, 2), 'xx'))
    when ses.p1text = 'idn' then -- https://tanelpoder.com/files/Oracle_Latch_And_Mutex_Contention_Troubleshooting.pdf
      case when ses.p1 <= 131072 then
        (select sql_text from v$sql s where mod(s.hash_value, 131072) = ses.p1)
       else
        (select sql_text from v$sql s where     s.hash_value          = ses.p1)
      end
   else
     to_char(ses.p1)
   end p1, -- Might be 'TCP' for p1text = 'driver id'
   ------------------------------------------------------

   ses.p2text, ses.p2, ses.p3text, ses.p3,
   ses.row_wait_block#   ses_row_wait_block#, ses.row_wait_file#    ses_row_wait_file#, ses.row_wait_obj#     ses_row_wait_obj#, ses.row_wait_row#     ses_row_wait_row#,
-- Segment related information. Requires uncommented join to dba_extents and dba_indexes below
-- ext.owner          seg_owner,  ext.segment_name   seg_name, ind.table_name table_name,
-- --------------------------------------------------------------------------------------
-- ses.p1raw, ses.p2raw, ses.p3raw,
   ses.module                                  ses_module, -- sql.module    sql_module, -- sql.module_hash,  ses.program ses_program,
   ses.action                                  ses_action, -- sql.action    sql_action, -- sql.action_hash,
   prc.program                                 prc_program,
   prc.pname                                   prc_pname,
   --
   sql.persistent_mem                          sql_persistent_mem,
   sql.runtime_mem                             sql_runtime_mem,
   sql.sharable_mem                            sql_sharable_mem,
   sql.typecheck_mem                           sql_typecheck_mem,
   --
   round(prc.pga_alloc_mem    /1024/1024,1)    prc_pga_alloc_mb   ,    -- >> PGA memory currently allocated by the process
   round(ses.pga_tunable_mem  /1024/1024,1)    ses_pga_tunable_mb ,    -- >> The amount of tunable PGA memory (in bytes).
   round(prc.pga_freeable_mem /1024/1024,1)    prc_pga_freeable_mb,
   round(prc.pga_max_mem      /1024/1024,1)    prc_pga_max_mb     ,
   round(prc.pga_used_mem     /1024/1024,1)    prc_pga_used_mb    ,
-- sql.parsing_schema_name  sql_parsing_schema_name, -- sql.parsing_schema_id, sql.parsing_user_id,
-- prc.username prc_username, /* oracle */
-- --------------------------------------------------------------
-- trx.used_ublk = trx.ubablk - trx.start_ubablk + 1
-- trx.used_urec = trx.ubarec - trx.start_ubarec + 1
   trx.used_ublk,  -- trx.start_ubablk  trx_start_ubablk, trx.ubablk  trx_ubablk      ,
   trx.used_urec,  -- trx.start_ubarec  trx_start_ubarec, trx.ubarec  trx_ubarec,
   --
   trx.start_ubafil trx_start_ubafil, trx.ubafil  trx_ubafil,
   trx.start_ubasqn trx_start_ubasn , trx.ubasqn  trx_ubaseq,
-- --------------------------------------------------------------
   sql.program_id    ses_program_id,
   sql.program_line# ses_program_line#,

   --
   sql.direct_reads                                     sql_direct_reads,
   sql.buffer_gets                                      sql_buffer_gets,
   --
   sql.direct_writes                                    sql_direct_writes,
   sql.disk_reads                                       sql_disk_reads,
   trx.phy_io                                           trx_phy_io, trx.log_io trx_log_io,
   round(sql.user_io_wait_time /1e6,2)                  sql_user_io_wait_time_s,
   sql.physical_read_bytes                              sql_phys_read_bytes,
   round(case when sql.disk_reads > 0 then sql.physical_read_bytes / sql.disk_reads / 1024 end, 3)  "16K?",
  '****************',
   sql.fetches                                          sql_fetches,
   trx.cr_get                                           trx_cr_get,
   --
   sql.physical_read_requests                           sql_phys_read_requests,
   sql.physical_write_bytes                             sql_phys_write_bytes,
   sql.physical_write_requests                          sql_phys_write_requests,
   --
-- ses.seconds_in_wait,                                        -- >> Deprecated in favor of time_since_last_wait_micro and wait_time_micro
-- ses.wait_time,                                              -- >> Deprecated in favor of wait_time_micro and state
-- com.command_name,                                           -- ses.command, sql.command_type,
   ses.last_call_et,                                           -- time in seconds since status = 'INACTIVE' or 'ACTIVE'

   ses.sql_id,                     ses.sql_child_number,
   ses.sql_exec_id - 16*1024*1024  sql_exec_id_0,
-- sql.sql_id             ,   sql.child_number,
   ses.logon_time,
-- ses.action_hash, ses.module_hash,
-- ses.blocking_instance, ses.blocking_session, ses.blocking_session_status,
   ses.client_identifier,
   ses.client_info,
-- ses.con_id,

   ses.external_name,
-- ses.creator_addr, ses.creator_serial#,
   ses.current_queue_duration,
   ses.ecid,
-- ses.failed_over, ses.failover_method, ses.failover_type,
-- ses.final_blocking_instance, ses.final_blocking_session, ses.final_blocking_session_status,
-- ses.fixed_table_sequence, /* number that increases every time the session completes a call to the database and there has been an intervening select from a dynamic performance table */
   ses.lockwait,
-- ses.logon_time,
-- ses.machine,
-- ses.ownerid,
-- ses.paddr,


-- ses.plsql_debugger_connected,
   ses.plsql_entry_object_id, ses.plsql_entry_subprogram_id,
   ses.plsql_object_id      , ses.plsql_subprogram_id,
-- ses.port,

-- ----------------------
-- ses.prev_child_number,
-- ses.prev_exec_id,
-- ses.prev_exec_start,
-- ses.prev_hash_value,
-- ses.prev_sql_addr,
-- ses.prev_sql_id,
-- ----------------------
-- ses.process,
-- ses.program,
   ses.resource_consumer_group,
-- ses.saddr,
-- ses.server,
-- ses.service_name,
-- ses.session_edition_id,
-- ses.shard_ddl_status,
-- ses.sid,
-- ses.sql_address,

-- ses.sql_exec_start,
-- ses.sql_hash_value,
-- ses.sql_id,

-- ses.sql_trace, ses.sql_trace_binds, ses.sql_trace_plan_stats, ses.sql_trace_waits,
-- prc.addr                                prc_addr,

   prc.con_id                              prc_con_id,
-- prc.execution_type                      prc_execution_type, -- THREAD (Windows) , PROCESS (Linux)
   prc.latchspin                           prc_latchspin,
   prc.latchwait                           prc_latchwait,
   prc.numa_curr                           prc_numa_curr,
   prc.numa_default                        prc_numa_default,

   prc.pid                                 prc_pid,
   prc.tracefile                           prc_tracefile,
   prc.traceid                             prc_traceid,
   prc.username                            prc_username,
-- ses.sql_translation_profile_id,
   prc.terminal                            prc_terminal,
   ses.terminal                            ses_terminal,
   ses.top_level_call#                     ses_top_level_call#,
-- ses.type,              -- USER, ....
-- ses.user#,

-- sql.address,
   sql.application_wait_time sql_application_wait_time,
   sql.avoided_executions    sql_avoided_executions,
-- sql.bind_data,

-- sql.child_address,
-- sql.child_latch,

   sql.cluster_wait_time       sql_cluster_wait_time,
   sql.concurrency_wait_time   sql_concurrency_wait_time_ms,
-- sql.con_id,


   sql.ddl_no_invalidate       sql_ddl_no_invalidate,
   sql.end_of_fetch_count      sql_end_of_fetch_count,
   sql.exact_matching_signature, sql.force_matching_signature,


   sql.full_plan_hash_value    sql_full_plan_hash_value, -- sql.plan_hash_value,
-- sql.hash_value,
-- sql.im_scans, sql.im_scan_bytes_inmemory, sql.im_scan_bytes_uncompressed,
   sql.invalidations           sql_invalidations,
   sql.loaded_versions         sql_loaded_versions,
   sql.loads                   sql_loads,
-- sql.io_cell_offload_eligible_bytes, sql.io_cell_offload_returned_bytes, sql.io_cell_uncompressed_bytes, sql.io_interconnect_bytes,
-- sql.is_bind_aware           sql_is_bind_aware,
-- sql.is_bind_sensitive      "sql bind sens?",
-- sql.is_obsolete            "sql obsolete?",
-- sql.is_reoptimizable       "sql reoptim?",
-- sql.is_resolved_adaptive_plan "sql rlvd adpt. pln?",
   sql.is_rolling_invalid,
   sql.is_rolling_refresh_invalid,
   sql.is_shareable,
-- sql.java_exec_time            sql_java_exec_time,
   sql.plsql_exec_time           sql_plsql_exec_time,
   sql.kept_versions,
   sql.last_active_time,
   sql.last_load_time, sql.first_load_time,
   sql.literal_hash_value,

   sql.locked_total,

   sql.object_status,
-- sql.old_hash_value,
   sql.open_versions,
   sql.optimized_phy_read_requests,
   sql.optimizer_cost,
-- sql.optimizer_env, sql.optimizer_env_hash_value,
   sql.optimizer_mode,
   sql.outline_category,
   sql.outline_sid,
   sql.parse_calls,
   sql.pinned_total,
   sql.px_servers_executions,
-- sql.remote,
-- sql.result_cache,

   sql.serializable_aborts,
-- sql.service, -- sql.service_hash,
   sql.sorts,
   sql.sqltype,
-- sql.sql_id,
   sql.sql_patch,
   sql.sql_plan_baseline,
   sql.sql_profile,
   sql.sql_quarantine,
   sql.type_chk_heap,
   sql.users_executing     sql_users_executing,
   sql.users_opening       sql_users_opening,
-- prc.addr,
-- prc.con_id,
-- prc.execution_type,
   prc.latchspin,
   prc.latchwait,
-- prc.numa_curr,   prc.numa_default,
   --
   prc.spid prc_spid, -- prc.sosid prc_sosid,  prc.stid prc_stid,
-- prc.terminal,
-- trx.addr, -- ses.taddr,
   trx.con_id,
   trx.cr_change,
   trx.dependent_scn,
-- trx."DSCN-B", trx."DSCN-W", -- Obsolete, equal to dscn_base and dscn_wrap
   trx.dscn_base, trx.dscn_wrap,
   trx.flag,
   trx.name,
   trx.noundo,
   trx.prv_xid,
   trx.prv_xidslt,
   trx.prv_xidsqn,
   trx.prv_xidusn,
   trx.ptx,
   trx.ptx_xid,
   trx.ptx_xidslt,
   trx.ptx_xidsqn,
   trx.ptx_xidusn,
   trx.recursive,
-- trx.ses_addr,
   trx.space,
   trx.start_date         trx_start_date, -- trx.start_time,
   trx.start_scn          trx_start_scn,
-- trx.start_scnb, trx.start_scnw,
   --
   trx.start_uext,
   trx.xid,
-- --------------  DBMS_TRANSACTION.local_transaction_id returns something like
--                     11.66.843271
   trx.xidusn ,    --  11
   trx.xidslot,    --     66
   trx.xidsqn      --        843271
from
   v$session ses                                                                                                     join
   v$process prc     on ses.paddr                               = prc.addr                                      left join
   v$sql         sql on ses.sql_id                              = sql.sql_id and
                        ses.sql_child_number                    = sql.child_number                              left join
   v$transaction trx on ses.taddr                               = trx.addr                                      left join
   v$sql_monitor mon on ses.sid                                 = mon.sid               and
                        ses.serial#                             = mon.session_serial#   and
                        ses.sql_id                              = mon.sql_id            and
                     --
                     -- Use nvl(…) because, apparently, in CREATE TABLE statements, sql_exec_id is
                     -- not recorded in ses...
                     --
                        nvl(ses.sql_exec_id, mon.sql_exec_id)   = mon.sql_exec_id                               left join
-- v$sqlcommand  com on ses.command          = com.command_type                                                 left join
   v$px_session  pxs on ses.sid              = pxs.sid and
                        ses.serial#          = pxs.serial# /*                                                   left join
   dba_extents   ext on ses.p1text = 'file#' and -- event in( 'db file sequential read') and
                        ext.file_id = ses.p1 and
                        ses.p2text = 'block#' and ses.p2 between ext.block_id and ext.block_id + ext.blocks     left join
   dba_indexes   ind on ext.owner        = ind.owner and
                        ext.segment_name = ind.index_name   */

where
   ses.sid         != sys_context('USERENV', 'SID')     and -- Exclude executing session
-- ses.sid in (177, 236, 1658)
-- ses.osuser       = sys_context('userenv', 'os_user') and
-- ses.module       = 'powershell.exe' and
-- ses.status      != 'INACTIVE'  and
-- ses.program      = 'powershell.exe' and
-- prc.pname is null  -- Only show one v$session record for statements executed in parallel
   prc.background  is null and  -- 1 for a SYSTEM background process; NULL for foreground processes or non-SYSTEM background processes
-- trx.start_date is not null and
-- osuser not in ('oracle') and
   1=1
order by
   pxs.qcsid,
   ses.status,
   ses.last_call_et
;

See also

v$active_session_history.
Oracle Dynamic Performance Views
ses.sql is a small SQL script, intended to be stored at a location that $SQLPATH points at, that displays some rudimentary information about sessions.
http://www.adp-gmbh.ch/ora/concepts/session.html
Joining v$session with v$sql to get the most recently executed SQL statement.
Oracle Manageability Monitor Lite Process (MMNL)
x$ksuse

Index

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1740536008, '3.142.220.14', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/installed/dynamic-performance-views/session/index(914): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78