SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Timestamps and multiple table transactions

 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
Timestamps and multiple table transactions
Author Message
cdin
Guest





Post Timestamps and multiple table transactions Reply with quote
Hi,

We're evaluating DB Audit and we had a couple questions.

We are planning to use DB Audit to only log the data changes of certain tables. We would be using our own software to display data change logs to the end user and not use the built-in reporting tools. Our intent is to show the user the history of changes relevant to what the user is working on. Does anyone have experience using DB Audit in a similar manner?

Additionally, has anyone dealt with the issue of multiple tables being associated with a single transaction? For instance, if the user makes a change that touches multiple tables, is there an easy way to associate the data log changes among all the tables to that particular user action? It appears to us that only the only option is to use the column AUDIT_TIMESTAMP plus the primary keys for the original tables.

If that's the case, in the audit tables (for Oracle 10i), DB Audit uses a DATE datatype for the column AUDIT_TIMESTAMP. We're looking for more precision and were wondering, is it possible to set DB Audit to use TIMESTAMP as the datatype for that column as it carries fractions of a second?

Thanks,
Chris
Fri Aug 29, 2008 10:36 am
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
In addition to primary keys and AUDIT_TIMESTAMP you should use the session id which is also recorded in the audit trail; together this will provide you with a unique combination. The precision of datetime column AUDIT_TIMESTAMP is sufficient for this purpose.

No you cannot change AUDIT_TIMESTAMP data type to Oracle's TIMESTAMP. audit record header columns are not customizable.


Just in case, if you are not aware, DB Audit provides an API that external programs and scripts can use to set audit settings, configure filters and fetch data. The data output format is customizable, by default XML is used, but other formats are also supported.
Fri Aug 29, 2008 12:50 pm View user's profile Send private message
cdin2



Joined: 03 Sep 2008
Posts: 2
Country: United States

Post Reply with quote
How does the session id tie back to the data audit tables? I assume that you're talking about the column in the AUD$ table? I'm not quite understanding how I would use it to get to a "transaction id" across multiple tables.

Apologies for creating a second userid, but for whatever reason, the forum software is neither recognizing my password for the first account, nor letting me reset the password.
Wed Sep 03, 2008 2:55 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Please disregard the reference to session id, it is incorrect for Oracle. Sorry about that misleading reference.

Yet, there is a known trick involving use of a custom procedure hook to get Oracle session id recorded in the audit trail tables. That will likely provide you with the required values. I will search the support system for the exact solution and post it here
Wed Sep 03, 2008 3:12 pm View user's profile Send private message
cdin2



Joined: 03 Sep 2008
Posts: 2
Country: United States

Post Reply with quote
Hi, just checking in to see if you'd managed to find the procedure hook to allow the session id to be recorded in the audit tables.
Fri Sep 19, 2008 11:48 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Ok, there is an ADVANCED button available on the Set Data-change Audit Options screen. You can use that button to "hook" your custom stored procedure. The procedure will be fed several parameters, including user name and expect you to return some other user name. What you can do here is appending session id (SID) to the start or end of the user name using some character separator between SID and user name. That new value will be saved in the username column and you can use it in your reports and queries.

To get a better idea, check examples of custom stored procedure available in DB Audit manual pages 108 - 110
Fri Sep 19, 2008 4:45 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.