audit statements

[newbie]

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

[nir_s]

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
declare
text varchar2(1024);
tracef utl_file.file_type;
audsid number;
eol varchar2(2);
begin
tracef:=utl_file.fopen('/tmp','audit.trc','a');
-- 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 )
loop
text := 'Cursor#= ' || cr.cno || eol ||
' Ctype= ' || cr.stat || eol ||
' SQL-Text= '|| cr.line || eol ||
' Bind vars= ' || eol;
utl_file.put_line(tracef,text);
end loop;
text := dbms_utility.format_call_stack;
utl_file.put_line(tracef,text);
utl_file.fflush(tracef);
utl_file.fclose(tracef);
end;
/

Usage example:

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

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

Cursor#= 3
Ctype=CURBOUND
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

Regards,
Nir