Trigger to capture SQL statement and user info

What you are asking for is a TRACE!
Set trace parameter's to on (true) and use tkprof to analyze the trace file.

In a sepatrate case it's possible based on statement based trigger using V$session, V$sql identifying session_id.

To solve it systematically I'd recommend you to set on auditing.

 

Check out auditing:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/audit.htm#1108

If you don't wish to go to that level, you should be able to come up with your own using triggers and PL/SQL.  Just beware that your own can always be bypassed by any privledged/knowledgable user (then again, so can auditing).

This doesn't capture the SQL but this can be added with a little more work (just don't have time right now to get this part).
------------------------------------------------
drop table myAudit;
create table myAudit(
      username varchar2(100),
      whichtable varchar2(100),
      DMLtimestamp varchar2(100),
      terminal varchar2(100),
      ipaddress varchar2(100),
      osuser varchar2(100),
      DMLoperation  varchar2(100)
)
/


drop table tab1;
create table tab1 (
      col1 varchar2(100)
)
/

create or replace trigger tab1_trig
after insert or update or delete on tab1
for each row
declare
      PRAGMA AUTONOMOUS_TRANSACTION;
      DMLop varchar2(6);
begin
      if inserting then DMLop := 'Insert';
      elsif deleting then DMLop := 'Delete';
      elsif updating then DMLop := 'Update';
      else DMLop := '???';
      end if;

      insert into myAudit values(
            sys_context('userenv', 'session_user'),
            'TAB1',
            systimestamp,
            sys_context('userenv', 'terminal'),
            sys_context('userenv', 'ip_address'),
            sys_context('userenv', 'os_user'),
            DMLop
      );
      commit;

end;
/

show errors

insert into tab1 values('a');
insert into tab1 values('b');
update tab1 set col1 ='Z' where col1='b';
delete from tab1 where col1='Z';
commit;

select * from myAudit;

we set up auditing for DDL activities, but DML on some critical tables need to be captured. We had to opt for triggers, as auditing can't be done at object and user level and can't capture the actual statement. Capturing the statement part looks to be tricky.

I can start with what slight suggested. Try to get session id as jan suggested.

If any could help me with that, its great. I am also short of time on this -:)

Not sure a simple trigger will work for you.

To get the entire SQL you can use:
select sql_text from v$sqltext
           where address = (select sql_address from v$session where AuDsid = SYS_CONTEXT('USERENV','SESSIONID'))
           order by piece ;

Where I think it will break down is whoever fires the trigger will need select on the V$ views.  Also, BIND variables don't show up in v$sqltext.

hi

the best ever way to capture these is auditing but as per u require the wrapper for auditing i think
create a table with all details and with dml trigger u can insert the values into the table with PRAGMA AUTONOMOUS_TRANSACTION ...

the slightwv has given the perfect answer ..... just make it as PRAGMA AUTONOMOUS_TRANSACTION thats all

regards

create table myAudit(
     username varchar2(100),
     whichtable varchar2(100),
     DMLtimestamp varchar2(100),
     terminal varchar2(100),
     ipaddress varchar2(100),
     osuser varchar2(100),
     DMLoperation  varchar2(100)
)
/


drop table tab1;
create table tab1 (
     col1 varchar2(100)
)
/

create or replace trigger tab1_trig
after insert or update or delete on tab1
for each row
declare
     PRAGMA AUTONOMOUS_TRANSACTION;
     DMLop varchar2(6);
begin
     if inserting then DMLop := 'Insert';
     elsif deleting then DMLop := 'Delete';
     elsif updating then DMLop := 'Update';
     else DMLop := '???';
     end if;

     insert into myAudit values(
          sys_context('userenv', 'session_user'),
          'TAB1',
          systimestamp,
          sys_context('userenv', 'terminal'),
          sys_context('userenv', 'ip_address'),
          sys_context('userenv', 'os_user'),
          DMLop
     );
     commit;

end;
/

show errors

insert into tab1 values('a');
insert into tab1 values('b');
update tab1 set col1 ='Z' where col1='b';
delete from tab1 where col1='Z';
commit;

select * from myAudit;

Hi

1. enable the oracle auditing option to view the sql and session information

Is there any function that gives me the name of triggering table.... in our case tab1
I tried dbms_stanadard.dictionary_obj_name
-- It works fine when I do the operation as owner, but if I issue command on user.tab1
then its not logging any thing.
tried ora_dict_obj_name too.....

Any ideas!
Thx

 

I believe you will have to hard code the table name in the trigger itself.  Can you explain whay you would need this since the trigger is table specific?  I'm not aware of any system level trigger that will work for you.

I am calling a standard package/procedure in trigger. I may have trigger on multiple tables, calling this same package.

All you need to do is hardcode the parameter to the package in the trigger.


for example:

create or replace procedure bubba(table_name in varchar2) ...


create or replace trigger tab1_trigger
...
bubba('TAB1');
...

create or replace trigger tab2_trigger
...
bubba('TAB2');
...

Thank you very much...