If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Hybrid View

Extracting all DML staments from sys.aud$

I have enabled object auditing (select/update/delete).I want the sql statement associated with the audited records.How to extract the sql statements(i.e which column can me matched from sys.aud$ and v$sqltext etc).Pls. let me know asap.

Suresh,
Auditing(sys.aud$) can only help you find out which user made the changes and/or what he did with the Table data(insert/update/delete).

v$sqltext and v$sqlarea will only help you find the recent sql statements executed, old ones being phased out if they are not being used. And also when bind variables are used(recommended), then the sqls in these views will just show the identifiers, instead of actual values used.

After going through all notes and forums,i did the following to extract sql through aud$ but it didnt work.

1.I moved sys.aud$ to system.aud$ and created view aud$ in sys with system.aud$ table.rerun the cataudit.sql in sys schema.
2.Gave all permissions on system.aud$ to sys.
3.Created trigger on sys schema on system.aud$(after insert by each row) and insert the new.sessionid,new.userid,new.timestamp#.
4.Created a table x on scott and enabled all audit on this table.
5.inserted a row in this table.system.aud$ captures this event and my trigger is also fired but it stores only the new.sessioid,new.timestamp,new.userid and when i merge this query with
v$session and v$sqlarea,it fails.My insert statement in the trigger is

insert into dummy select :new.userid,:new.sessionid,:new.timestamp# from dual;
This works fine.
i execute another sql script select sql_text from v$sqlarea where
address =(select prev_sql_Addr from v$session where audsid= what i stored in my dummy table.This gives the exact sql what i ran on the table x.
If i put this script in the trigger the script fails.Pls. help me how to extract the sql by merging aud$ table/v$sqlarea/v$session in the trigger.