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

Thread: Extracting all DML staments from sys.aud$

  1. #1
    Join Date
    Jun 2000
    Location
    chennai,tamil nadu,india
    Posts
    159

    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.

  2. #2
    Join Date
    Jun 2000
    Location
    chennai,tamil nadu,india
    Posts
    159
    Any Oracle Guru's who did this in past.Pls. help me.

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you better go back and RTFM again and see what does audit *REALLY* audit

  4. #4
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    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.

    Maybe you can use Triggers to log the required changes.

    HTH.
    -- Dilip

  5. #5
    Join Date
    Jun 2000
    Location
    chennai,tamil nadu,india
    Posts
    159
    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.

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