History Set and Audit records

[sandeep3791]

HI All,

I am trying to understand the best suit for my purpose,

I want to maintain the history of values for one field on the table.

I have used Enable Audit for table and Whitelisting to make this happen.

I am able to get a record created every time in the 'sys_audit' table for any change to that field.

I want to know how do I send this historic data to external requesting system.

I read that it is not good to directly query sys_audit table and we should use sys_history_set, But I don't really see a difference when I have only one field enables for audit. so my audit table only has data for that one field only. and even if I go to sys_history_set I again get the same data.

I have already gone through all possible docs/wiki material on this topic.

Kindly suggest why it is not recommended to use sys_audit for querying? can I use it in my case?

thanks,

Sandeep Patil

[Jon Barnes]

You are correct not to use sys_history_set. That table is filled in on demand from the sys_audit table anyway, and it won’t have data until you Click the history link for a table or fill it from a script which queries sys_audit anyway.

So you actually can query sys_audit if you must, with one condition: that you only query by specific document ids. This will use an index and is generally better, but keep in mind that even using that query, it can be slow if there are multiple millions of records in your audit tables.

So, would I recommend it? No. But I have done it before when there wasn’t a better solution but was careful to only query by specific document references and I knew there would only be a small number of documents I was querying in sys_audit at a given time.