OS User on Oracle DBA History views

Question

We need to monitor all SQL-s coming from a certain Application Server to an Oracle DB.

For this, we are running the following query:

SELECT
   h.sample_time,
   u.username,
   h.program,
   h.module,
   h.machine,
   s.sql_text
FROM
   DBA_HIST_ACTIVE_SESS_HISTORY h,
   DBA_USERS u,
   DBA_HIST_SQLTEXT s
WHERE sample_time >= SYSDATE - 0.1
   AND h.user_id = u.user_id
   AND h.sql_id = s.sql_iD
   AND u.USERNAME = '<app_server_name>'
   AND h.MACHINE = ('<app_schema_name>')
ORDER BY h.sample_time

Problem with this:

Missing OS User of the Application Server machine.

Could not find anything about OS User on views DBA_HIST_ACTIVE_SESS_HISTORY and DBA_HIST_SQLTEXT. (Meanwhile there is a column OSUSER on view V$SESSION - the non-history version of DBA_HIST_ACTIVE_SESS_HISTORY)

I took a look on columns of DBA %HIST% views:

select t.owner, t.table_name, t.column_name, t.data_type,
t.data_precision, t.data_scale
from dba_tab_cols t
where t.owner = 'SYS'
and t.table_name like '%DBA_HIST%'
and t.column_name like '%OS%'
order by t.table_name, t.COLUMN_NAME

but could not find anything related to OS User info.

Any idea of how I can have the OS User data on my first query ?

best regards

Altin

Answer 1

First, is there a reason why you aren't enabling auditing? That would be the only way to capture 100% of the queries by a given user.

Auditing isn't a perfect fit in all cases, particularly if the volume is excessive, so looking at ASH data does make sense as an alternative approach. But keep in mind that dba_hist_active_sess_history is only populated at an AWR snapshot interval (typically 1 hour apart, but configurable). Your SYSDATE - 0.1 filter will only work if it faithfully fires each hour and the AWR interval is indeed an hour or less. Skip an hour or two and you'll miss your data. Also the dba_hist version is only 1 out of every 10 samples, so can miss a lot of small stuff. You are much better off querying the in-memory version gv$active_session_history, which has 10x more entries and is real-time. You might want to set up something to hit this every minute, or every 10 minutes. It typically keeps its info for a few hours at least. That'd be a lot more accurate than the dba_hist version. Be aware that even this isn't 100% comprehensive. It gets its data by sampling the wait interface once per second. If a query executes in under a second and isn't repeatedly executed, you may not ever see it. But it's close enough for most purposes.

You will not find osuser anywhere in ASH data. If you want this, you will need to create a logon trigger that captures session info for every new session. You can save space by only capturing it if it's from the machine in question, whatever kind of filters make sense for you. But then you have a full set of v$session attributes at your disposal within the trigger, plus additional SYS_CONTEXT attributes. Write what you need to a log table keyed by sid,serial#,inst_id,logon_time. Later you can join ASH data (gv$active_session_history) to this log table using session_id/session_serial#/inst_id and add whatever additional attributes you have there for the session.

Answer 2

Below is what proposed by Paul W. (or at least the way I understood it from his comments). Wrote as an answer to have the code formatted for better understanding:

-- create session info table
create table DB_LOGON_TAB 
(
logon_time date, 
session_id number, 
session_serial number, 
inst_id number, 
os_user varchar2, 
....
) 
... ;

-- db after logon trigger
CREATE OR REPLACE TRIGGER LOGON_AUDIT_TRIGGER AFTER LOGON ON DATABASE 
BEGIN
<insert session data for monitored users/hosts to DB_LOGON_TAB>
END;
    
-- Query:

SELECT
   h.sample_time,
   u.username,
   h.program,
   h.module,
   h.machine,
   s.sql_text,
   -- my needed field
   x.os_user
FROM
   GV$ACTIVE_SESSION_HISTORY h,
   DBA_USERS u,
   DBA_HIST_SQLTEXT s,
   -- my table
   DB_LOGON_TAB x
WHERE sample_time >= SYSDATE - 0.1
   AND h.user_id = u.user_id
   AND h.sql_id = s.sql_iD
   -- join to my table
   AND h.session_id = x.session_id
   AND h.session_serial# = x.session_serial 
   AND h.inst_id = x.inst_id
   -- continued
   AND u.USERNAME = '<app_server_name>'
   AND h.MACHINE = ('<app_schema_name>')
ORDER BY h.sample_time

Did I get your proposal correctly Mr. Paul W ?

best regards Altin