DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: audit statements

  1. #1
    Join Date
    Mar 2001

    audit statements

    Hi all, I'm wonderinng if there is a way that I can track (audit) all insert,update,delete activities of a user with the actual statement of what he/she did. For example, I could track the table name of what activities were performed on that table but I also want to track the ACTUAL statement that was ran (e.g select userid from USR_TABLES). Is there a way to do this, logminer is not an option for me since this database in in noarchive mode. thnks all

  2. #2
    Join Date
    Dec 2001
    Hi newbie,

    I was also faced with this kind of issue in my company.
    The Audit trail utility does not suitable to this kind of "mission".
    Oracle has not built yet a good utility which can catch all the dml statements in the database.
    The best way to do it is to focus on specific table or on some tables,by building a special trigger ,as detailed in the bellow example:

    SQL> connect "/ as sysdba"
    grant select on SYS.V_$SQL to scott;
    grant select on SYS.V_$SQL_BIND_DATA to scott;
    grant select on SYS.V_$SQL_CURSOR to scott;
    grant select on SYS.V_$SESSION to scott;
    grant create trigger to scott;
    SQL> connect scott/tiger

    create or replace trigger dept_trig
    after insert or update on dept
    for each row
    text varchar2(1024);
    tracef utl_file.file_type;
    audsid number;
    eol varchar2(2);
    -- change the path to your local environment
    text := '';
    eol := chr(10); -- for Unix
    eol := chr(12) || chr(10); -- for Windows/VMS
    select userenv('SESSIONID') into audsid from dual;
    for cr in (select q.sql_text line, c.curno cno, c.status stat
    from v$sql q, v$sql_cursor c, v$session s
    where s.audsid=audsid and s.prev_sql_addr=q.address and
    q.address=c.parent_handle )
    text := 'Cursor#= ' || cr.cno || eol ||
    ' Ctype= ' || cr.stat || eol ||
    ' SQL-Text= '|| cr.line || eol ||
    ' Bind vars= ' || eol;
    end loop;
    text := dbms_utility.format_call_stack;

    Usage example:

    SQL> insert into dept (deptno, dname, loc) values (35, 'teszt2', 'CCCC');

    It produces the following lines in audit.trc (in location as specified in utl_file.fopen):

    Cursor#= 3
    SQL-Text= insert into dept (deptno, dname, loc) values (35, 'teszt2', 'CCCC')
    Bind vars=
    ----- PL/SQL Call Stack -----
    object line object
    handle number name
    c2e39268 24 SCOTT.DEPT_TRIG


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width