v$session
shows information about a «current» session. 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
. username
. 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_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;
v$session_blockers
. 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 |
sys_context('userenv','SESSIONID')
to find out which session is the "own one". dbms_support.mysid
can be used: where sid = sys.dbms_support.mysid
. module
, action
and client_info
can be set with procedures set_module
, set_client_info
and set_action
of the dbms_application_info
package v$session_longops
for an example). 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';
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
seems to start with 224 for every sql_id
when Oracle is started. type
can be used to identify background (type='BACKGROUND'
) and foreground (type='USER'
) processes. saddr
(the session address) might be joined to dba_kgllock
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 |
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;
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 ;
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 ;
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
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 ;