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

Paul W · Accepted Answer · 2024-03-01 20:07:41Z

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.

answered Mar 1, 2024 at 20:07

Paul W

12.8k2 gold badges9 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

altink Over a year ago

Thank You Paul. I am not using audit because of the very high volume it would result if I was to audit the application schema owner for every (!) action. I intend to run the monitoring query every 1 minute - preferred option . Say 5, or 10 at maximum. Your suggestion is I must replace DBA_HIST_ACTIVE_SESS_HISTORY with gv$active_session_history. Question 1: what about using V$ACTIVE_SESSION_HISTORY ?

altink Over a year ago

Next, to have the OS USER info, and knowing that my query will run every 1 minute, Question 2: Can I skip the usage of the logon trigger and bind gv$active_session_history with v$session by triplet session_id/session_serial#/inst_id (at this point, on both V$ACTIVE_SESSION_HISTORY and V$SESSION, cannot find a field for Instance Number )

Paul W Over a year ago

@altink, gv$ is for RAC. If you are single instance, use the v$ version. You can join to v$session only if the session is still present. Once it logs out, you lose that information, which is why a logon trigger is necessary.

altink Over a year ago

Thank You very much Paul W. So, I will do as below: -- 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 monitred user/hosts to DB_LOGON_TAB> END; ... continued

altink Over a year ago

SELECT h.sample_time, u.username, h.program, h.module, h.machine, s.sql_text, -- needed field x.os_user FROM GV$ACTIVE_SESSION_HISTORY h, DBA_USERS u, DBA_HIST_SQLTEXT s, -- my tab 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 tab 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

altink · Accepted Answer · 2024-03-06 17:19:09Z

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

Collectives™ on Stack Overflow

OS User on Oracle DBA History views

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related