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.
Any Oracle Guru's who did this in past.Pls. help me.
you better go back and RTFM again and see what does audit *REALLY* audit
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.
Maybe you can use Triggers to log the required changes.
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.
Click Here to Expand Forum to Full Width