Which program are users using
The following query returns the programs with which different users connect to the database:
select
count(*) cnt,
-- regexp_replace(ash.program, 'oracle@(.*) \((P|J)\d..\)', 'oracle@\1 (\2...)') program,
ash.program,
ash.module,
usr.username,
ash.user_id
from
dba_hist_active_sess_history ash left join
dba_users usr on ash.user_id = usr.user_id
where
usr.oracle_maintained = 'N' and
not regexp_like(ash.program, 'oracle@(.*) \((P|J)\d..\)')
group by
-- regexp_replace(ash.program, 'oracle@(.*) \((P|J)\d..\)', 'oracle@\1 (\2...)'),
ash.program,
ash.module,
usr.username,
ash.user_id
order by
usr.username
;
On the current system, it returned values such as MSACCESS.EXE, plsqldev.exe, EXCEL.EXE, ISServerExec.exe, sqlplus.exe, sqldeveloper.exe, sqldeveloper64W.exe, w3wp.exe, ReportingServicesService.exe, python.exe, JDBC Thin Client, spssengine.exe, nunit-console.exe, Microsoft.Mashup.Container.NetFX40.exe etc.
Big query
select
ash.sample_id,
-- ash.sample_time_utc,
-- ash.snap_id, -- join with dba_hist_ash_snapshot
ash.sample_time, -- compare with sample_time_utc
-- round( (ash.sql_exec_start - cast(ash.sample_time as date)) * 24 * 60 * 60) sql_running_since_approx_s,
round( ( cast(ash.sample_time as date) - ash.sql_exec_start ) * 24 * 60 * 60) running_s, -- approximate(1) running time of sql UNTIL snapshot
--
-- ash.user_id,
usr.username,
--
ash.event,-- ash.event_id,
ash.p1text, ash.p1,
ash.p2text, ash.p2,
ash.p3text, ash.p3,
ash.module,
-- ash.sql_id,
sql.sql_text,
ash.action,
ash.blocking_hangchain_info,
--
ash.blocking_inst_id,
ash.blocking_session,
ash.blocking_session_serial#,
ash.blocking_session_status,
--
ash.capture_overhead,
ash.client_id,
--
ash.dbid,
ash.con_dbid,
ash.con_id,
--
ash.current_block#, ash.current_file#, ash.current_obj#, ash.current_row#,
--
ash.dbop_exec_id,
ash.dbop_name,
ash.dbreplay_call_counter,
ash.dbreplay_file_id,
ash.delta_interconnect_io_bytes,
ash.delta_read_io_bytes,
ash.delta_read_io_requests,
ash.delta_time,
ash.delta_write_io_bytes,
ash.delta_write_io_requests,
ash.ecid,
ash.flags,
ash.force_matching_signature,
ash.in_bind,
ash.in_connection_mgmt,
ash.in_cursor_close,
ash.in_hard_parse,
ash.in_inmemory_populate,
ash.in_inmemory_prepopulate,
ash.in_inmemory_query,
ash.in_inmemory_repopulate,
ash.in_inmemory_trepopulate,
ash.in_java_execution,
ash.in_parse,
--
ash.in_plsql_compilation,
ash.in_plsql_execution,
ash.in_plsql_rpc,
--
ash.in_sequence_load,
ash.in_sql_execution,
ash.in_tablespace_encryption,
ash.instance_number,
ash.is_captured,
ash.is_replay_sync_token_holder,
ash.is_replayed,
ash.is_sqlid_current,
ash.machine,
--
ash.pga_allocated,
--
ash.plsql_entry_object_id,
ash.plsql_entry_subprogram_id,
ash.plsql_object_id,
ash.plsql_subprogram_id,
--
ash.port,
ash.program,
ash.px_flags,
ash.qc_instance_id,
ash.qc_session_id,
ash.qc_session_serial#,
ash.remote_instance#,
ash.replay_overhead,
ash.seq#,
ash.service_hash,
ash.session_id,
ash.session_serial#,
ash.session_state,
ash.session_type,
ash.sql_adaptive_plan_resolved,
--
ash.sql_child_number,
ash.sql_exec_id,
ash.sql_full_plan_hash_value,
--
ash.sql_opcode,
ash.sql_opname,
ash.sql_plan_hash_value,
ash.sql_plan_line_id,
ash.sql_plan_operation,
ash.sql_plan_options,
ash.temp_space_allocated,
ash.time_model,
ash.time_waited,
ash.tm_delta_cpu_time,
ash.tm_delta_db_time,
ash.tm_delta_time,
ash.top_level_call#,
ash.top_level_call_name,
ash.top_level_sql_id,
ash.top_level_sql_opcode,
ash.usecs_per_row,
ash.wait_class,
ash.wait_class_id,
ash.wait_time,
ash.xid,
--
ash.consumer_group_id
from
sys.dba_hist_active_sess_history ash left join
sys.dba_hist_sqltext sql on ash.sql_id = sql.sql_id and
ash.dbid = sql.dbid left join
sys.dba_users usr on ash.user_id = usr.user_id
-- where
-- ash.sample_time between to_date('2024-02-26 14:00:30', 'yyyy-mm-dd hh24:mi:ss') and
-- to_date('2024-02-26 14:00:40', 'yyyy-mm-dd hh24:mi:ss')
--
order by
ash.sample_time desc
;