Oracle logoff trigger to get sql's executed by session

Question

I have created a table which contains the host names of all the trusted sources. I have written a oracle log off trigger to fetch details of all the sql executed by that session if the connection's host is not amongst the snif_session table. I am taking the output to a utl_file output which contains the sid, hostname, time connected.

SQL> select * from snif_Session;

ALLOWED_HOST
--------------------------------------------------
RND1
WORKGROUP\RND1

The place where i am getting stuck is what query to use to get all the sql's executed by that particular session ( i can get the sid from v$mystat).

does this work best :

select a.sql_id
         ,b.sql_text 
from dba_hist_active_sess_history a
        ,dba_hist_sqltext b
where a.sql_id=b.sql_id

or

select s.sid
         , s.serial#
         , a.sql_text
from v$session s
    join v$sqlarea a 
            on a.hash_value = s.sql_hash_value ;

This is the code i have written (block) which i will be placing inside a trigger.

declare
    machine_id varchar2(50);
    val int;
    auth_terminal varchar2(50);
    check_machine varchar2(1000);
    mydate char(50);
    osuser_1 varchar2(50);
    sid_1 int;
    sql_query_1 varchar2(5000);
    machine_1 varchar2(50);
    trace_info UTL_FILE.FILE_TYPE;
begin
    select machine into check_machine 
    from v$session 
    where sid in (select distinct(sid) from v$mystat) ;
    select count(*) into val 
    from snif_session 
    where allowed_host=check_machine;
    if ( 1=val) then
        dbms_output.put_line(check_machine|| ' dont check host' );
    else
        dbms_output.put_line(check_machine || ' check host' );
    end if;
    select osuser,sid,machine 
    into osuser_1,sid_1,machine_1 
    from v$session 
    where sid in (select distinct(sid) from v$mystat);
    SELECT TO_char(systimestamp,'mm/dd/yyyy HH24:MI:SS') into mydate   
    FROM DUAL;
    dbms_output.put_line(mydate || sid_1 || '   ' || osuser_1 || '  '|| machine_1);
    trace_info := UTL_FILE.FOPEN('UTL_DIR', 'trace_info_file.txt', 'W');
    UTL_FILE.PUTF(trace_info,mydate||'  '||sid_1||'     '||osuser_1||'  '|| machine_1);
    UTL_FILE.FCLOSE(trace_info);
EXCEPTION
    WHEN utl_file.invalid_path THEN
       raise_application_error(-20000, 'ERROR: Invalid PATH FOR file.');
end;

I need to include the 'sql queries' also executed by session in the utl_file output.

I need to include the 'sql queries' also executed by session in the utl_file output — anudeepks, Sep 20 '14 at 06:26
What is the problem that you're trying to solve? My guess is that you really want to use auditing rather than writing a trigger. AWR will not have every statement that a session executed. At best, v$active_session_history will have any SQL statement that happened to be active at the top of a second and that has not been purged from the buffer yet. That is unlikely to be every SQL statement that a session executed particularly for long-running sessions. — Justin Cave, Sep 20 '14 at 06:56
Im tyring to log the sql queris executed by the session before the log off. using auditing would be an expensive process. — anudeepks, Sep 20 '14 at 07:03
Define "expensive"? Querying the AWR is time-consuming. Auditing generally doesn't add that much overhead. More importantly, though, what problem are you trying to solve? Auditing can log every SQL statement. Querying the AWR tables cannot. — Justin Cave, Sep 20 '14 at 07:04
The problem here is want to check the sql queries performed by session coming from host which are not a part of the snif_Session table ( only the trusted sources hostnames are a part of this table) — anudeepks, Sep 20 '14 at 07:09
But what is the underlying business problem? As I've said, it sounds like you're trying to audit SQL statements. If so, you should be using auditing. If you want every SQL statement, you want auditing. If you really just want a sample of the SQL statements, AWR is at least theoretically an option. Not knowing what problem you're trying to solve, it's difficult to know how to help you. — Justin Cave, Sep 20 '14 at 07:16

score 5 · Answer 1 · answered Sep 20 '14 at 07:04

"I need to include the 'sql queries' also executed by session"

Neither of your suggested queries will give you all the SQL executed by a session.

V$SESSION is a dynamic view, so it just shows what is happening in a session right now.

DBA_HIST_ACTIVE_SESS_HISTORY is a series of snapshots of running SQL. It is intended for performance profiling, and as such it is basically a random sub-set of active statements. Also, it is part of the Diagnostics and Tuning Pack: you will be in breach of your license if you use it without paying the additional charge.

It appears that what you really need is an audit trail. Instead of rolling your own, why not investigate the functionality Oracle already has? There's AUDIT to track DDL activity. There's Fine-grained Auditing to monitor lower-level DML. Find out more.

Oracle logoff trigger to get sql's executed by session

1 Answers1