RE: AW: auditing tables
We had implemeted the following design in my previous project (a Mortgage Origination System) to accomplish this. We had marketing tables to keep track of mortgage products, rates etc. These tables were query intensive. Inserts/Updates/Logical deletes were less. The primary key was made up with an sequence# and a revision number. A row with the revision number of 0 is the latest one. Another column (expiry_date) indicates whether the row is logically deleted or not. So whenver an update was done, a 'before row update' trigger used to capture the old values, get the max revision number + 1 for that sequence number, and reinsert the row into the same table. So auditing was easy. Prakash -Original Message- Sent: Tuesday, January 29, 2002 3:11 PM To: Multiple recipients of list ORACLE-L Now you're getting into the realm of Temporal or Time- Oriented Databases. Suppose you want to know what change Fred made on Tuesday. With your design, the audit row only shows what the old value was, not what the new value is. To find that, you have to find either the current production row, OR the next-most-recent change for that row in the audit table. Finding the next-most-recent row in an audit table is not a lot of fun, and can be a bit of a performance pig. And suppose the next change is a deletion. A typical way to track that is to record only the PK value of the deleted row. If you do that, then you've lost the 'new' value that Fred put in. So, if you regularly report on old-and-new values from the audit table, it makes sense to store them both for the same change. My most recent design looked something like this, with one row in AUD_TAB for each row changed, and one row in AUD_COL for each column changed in that row (inserts and updates only): AUD_TAB change_id (pk) table_name change_type pk_value AUD_COL change_id (fk) (pk) column_name(pk) old_value new_value Cheers. -Tom --- Jared Still <[EMAIL PROTECTED]> wrote: > I don't think you need two rows for updates. The old values > will be in the audit table, the new ones are in the production > table. > At least that's the way I've always done it. > Is there some other reason for saving both in the audit table? > On Tuesday 29 January 2002 03:00, Rachel Carmichael wrote: > > Update -- add two rows to the auditing table -- first with old > > values and type = O > > second with all the new values and type =N > > --- "Foelz.Frank" <[EMAIL PROTECTED]> wrote: > > > What I need is exactly what Oracle doesn't support. Logging "who" > > > changed "what" in a special area of our database. > > > > > > I think triggering the events will be much more specific and more > > > easy to change. > > > > > > In case all our applications use the same database and user, I am > > > trying to check out > > > what application is changing monitored tables (i.e. > > > c:\app\userapp\app.exe is changing table1). > > > What do you think of that ?? = Thomas B. Cox "Saepe in errore sed numquam in dubito" [EMAIL PROTECTED] http://www.geocities.com/tbcox23/ "The whole aim of practical politics is to keep the populace alarmed (and hence clamorous to be led to safety) by menacing it with an endless series of hobgoblins, all of them imaginary." --H.L. Mencken __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas B. Cox INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bala, Prakash INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: AW: auditing tables
you don't. But if there are multiple changes to the row, and you want to see what it looked like at a particular point in time, you could just extract it from the audit table instead of having to trace it back. Unless you WANT to recreate rollback segment functionality and Oracle's recovery process? --- Jared Still <[EMAIL PROTECTED]> wrote: > On Tuesday 29 January 2002 03:00, Rachel Carmichael wrote: > > Update -- add two rows to the auditing table -- first with old > values > > and type = O > > second with all the new values and type =N > > > > Rachel, > > I don't think you need two rows for updates. The old values > will be in the audit table, the new ones are in the production > table. > > At least that's the way I've always done it. > > Is there some other reason for saving both in the audit table? > > Jared > > > --- "Foelz.Frank" <[EMAIL PROTECTED]> wrote: > > > TNX for your answers. > > > > > > What I need is exactly what Oracle doesn't support. Logging "who" > > > changed > > > "what" in a > > > special area of our database. > > > > > > I think triggering the events will be much more specific and more > > > easy to > > > change. > > > > > > In case all our applications use the same database and user, I am > > > trying to > > > check out > > > what application is changing monitored tables (i.e. > > > c:\app\userapp\app.exe > > > is changing > > > table1). > > > What do you think of that ?? > > > > > > greets > > > > > > > Frank < > > > > > > > > > > > >Von: Rachel Carmichael [mailto:[EMAIL PROTECTED]] > > > > > > > >what sort of information are you looking to audit? if you want > any > > > >sort of detail, you are better off with triggers and possibly an > > > > > > audit > > > > > > >table. Oracle doesn't record WHAT has been changed, just that > the > > > >table was accessed. So you don't know the row etc... > > > > > > > >--- "Foelz.Frank" <[EMAIL PROTECTED]> wrote: > > > >> Hi all, > > > >> > > > >> does anyone have experience in using Oracle's possibilities of > > > >> auditing > > > >> a database ?? > > > >> > > > >> I am interested in performance questions i.e. is it a hughe > loss > > > > > > of > > > > > > >> performance > > > >> when auditing tables Inserts/Updates/Deletes. Should I use > > > > > > triggers > > > > > > >> instead > > > >> ? > > > >> > > > >> any hints (comments, websites, etc...) are welcome. > > > >> > > > >> > Frank < > > > >> > > > >> -- > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > -- > > > Author: Foelz.Frank > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) > 538-5051 > > > San Diego, California-- Public Internet access / Mailing > > > Lists > > > > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and > in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You > may > > > also send the HELP command for other information (like > subscribing). > > > > __ > > Do You Yahoo!? > > Great stuff seeking new owners in Yahoo! Auctions! > > http://auctions.yahoo.com __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: AW: auditing tables
no you don't need two rows... but if you have multiple updates to the row and want to see what it looked like at the beginning you'd have to spend time tracing them all the way back. Unless you WANT to recreate the rollback segment functionality? this way, you have a before and after image right there -- time tracked if you want --- Jared Still <[EMAIL PROTECTED]> wrote: > On Tuesday 29 January 2002 03:00, Rachel Carmichael wrote: > > Update -- add two rows to the auditing table -- first with old > values > > and type = O > > second with all the new values and type =N > > > > Rachel, > > I don't think you need two rows for updates. The old values > will be in the audit table, the new ones are in the production > table. > > At least that's the way I've always done it. > > Is there some other reason for saving both in the audit table? > > Jared > > > --- "Foelz.Frank" <[EMAIL PROTECTED]> wrote: > > > TNX for your answers. > > > > > > What I need is exactly what Oracle doesn't support. Logging "who" > > > changed > > > "what" in a > > > special area of our database. > > > > > > I think triggering the events will be much more specific and more > > > easy to > > > change. > > > > > > In case all our applications use the same database and user, I am > > > trying to > > > check out > > > what application is changing monitored tables (i.e. > > > c:\app\userapp\app.exe > > > is changing > > > table1). > > > What do you think of that ?? > > > > > > greets > > > > > > > Frank < > > > > > > > > > > > >Von: Rachel Carmichael [mailto:[EMAIL PROTECTED]] > > > > > > > >what sort of information are you looking to audit? if you want > any > > > >sort of detail, you are better off with triggers and possibly an > > > > > > audit > > > > > > >table. Oracle doesn't record WHAT has been changed, just that > the > > > >table was accessed. So you don't know the row etc... > > > > > > > >--- "Foelz.Frank" <[EMAIL PROTECTED]> wrote: > > > >> Hi all, > > > >> > > > >> does anyone have experience in using Oracle's possibilities of > > > >> auditing > > > >> a database ?? > > > >> > > > >> I am interested in performance questions i.e. is it a hughe > loss > > > > > > of > > > > > > >> performance > > > >> when auditing tables Inserts/Updates/Deletes. Should I use > > > > > > triggers > > > > > > >> instead > > > >> ? > > > >> > > > >> any hints (comments, websites, etc...) are welcome. > > > >> > > > >> > Frank < > > > >> > > > >> -- > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > -- > > > Author: Foelz.Frank > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) > 538-5051 > > > San Diego, California-- Public Internet access / Mailing > > > Lists > > > > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and > in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You > may > > > also send the HELP command for other information (like > subscribing). > > > > __ > > Do You Yahoo!? > > Great stuff seeking new owners in Yahoo! Auctions! > > http://auctions.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jared Still > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: AW: auditing tables
Tom, Well, I didn't include a design with my post. Audit tables always get their own PK in my book, along with a datestamp recording the time of the change and column indicating who made the change ( if that's available ). The old value(s) is/are always in the audit table, the new value is always in the production table. If a row is deleted, the whole thing goes in the audit table. My purpose for these has been for occasional auditing. I use code ( Perl, or course ) to generate the proper DDL and triggers for all tables I wish to audit, placing them in their own tablespaces. Your implementation of a separate table for column changes is intriguing, I'll compare it with what I normally do next time someone thinks they want their stuff audited. If someone wants to query this stuff on a regular basis, it needs to be designed and constructed appropriately. At that time I take Snodgrass and Kimball off the bookshelf. :) Jared On Tuesday 29 January 2002 12:10, Thomas B. Cox wrote: > Now you're getting into the realm of Temporal or Time- > Oriented Databases. > > Suppose you want to know what change Fred made on > Tuesday. With your design, the audit row only > shows what the old value was, not what the new > value is. To find that, you have to find either > the current production row, OR the next-most-recent > change for that row in the audit table. > > Finding the next-most-recent row in an audit > table is not a lot of fun, and can be a bit of > a performance pig. > > And suppose the next change is a deletion. A typical > way to track that is to record only the PK value > of the deleted row. If you do that, then you've > lost the 'new' value that Fred put in. > > So, if you regularly report on old-and-new values > from the audit table, it makes sense to store them > both for the same change. My most recent design > looked something like this, with one row in AUD_TAB > for each row changed, and one row in AUD_COL for > each column changed in that row (inserts and updates > only): > > AUD_TAB > change_id (pk) > table_name > change_type > pk_value > > AUD_COL > change_id (fk) (pk) > column_name(pk) > old_value > new_value > > Cheers. > -Tom > > --- Jared Still <[EMAIL PROTECTED]> wrote: > > I don't think you need two rows for updates. The old values > > will be in the audit table, the new ones are in the production > > table. > > At least that's the way I've always done it. > > Is there some other reason for saving both in the audit table? > > > > On Tuesday 29 January 2002 03:00, Rachel Carmichael wrote: > > > Update -- add two rows to the auditing table -- first with old > > > values and type = O > > > second with all the new values and type =N > > > > > > --- "Foelz.Frank" <[EMAIL PROTECTED]> wrote: > > > > What I need is exactly what Oracle doesn't support. Logging "who" > > > > changed "what" in a special area of our database. > > > > > > > > I think triggering the events will be much more specific and more > > > > easy to change. > > > > > > > > In case all our applications use the same database and user, I am > > > > trying to check out > > > > what application is changing monitored tables (i.e. > > > > c:\app\userapp\app.exe is changing table1). > > > > What do you think of that ?? > > = > Thomas B. Cox "Saepe in errore sed numquam in dubito" > [EMAIL PROTECTED] http://www.geocities.com/tbcox23/ > > "The whole aim of practical politics is to keep the > populace alarmed (and hence clamorous to be led to > safety) by menacing it with an endless series of > hobgoblins, all of them imaginary." --H.L. Mencken > > __ > Do You Yahoo!? > Great stuff seeking new owners in Yahoo! Auctions! > http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: AW: auditing tables
Now you're getting into the realm of Temporal or Time- Oriented Databases. Suppose you want to know what change Fred made on Tuesday. With your design, the audit row only shows what the old value was, not what the new value is. To find that, you have to find either the current production row, OR the next-most-recent change for that row in the audit table. Finding the next-most-recent row in an audit table is not a lot of fun, and can be a bit of a performance pig. And suppose the next change is a deletion. A typical way to track that is to record only the PK value of the deleted row. If you do that, then you've lost the 'new' value that Fred put in. So, if you regularly report on old-and-new values from the audit table, it makes sense to store them both for the same change. My most recent design looked something like this, with one row in AUD_TAB for each row changed, and one row in AUD_COL for each column changed in that row (inserts and updates only): AUD_TAB change_id (pk) table_name change_type pk_value AUD_COL change_id (fk) (pk) column_name(pk) old_value new_value Cheers. -Tom --- Jared Still <[EMAIL PROTECTED]> wrote: > I don't think you need two rows for updates. The old values > will be in the audit table, the new ones are in the production > table. > At least that's the way I've always done it. > Is there some other reason for saving both in the audit table? > On Tuesday 29 January 2002 03:00, Rachel Carmichael wrote: > > Update -- add two rows to the auditing table -- first with old > > values and type = O > > second with all the new values and type =N > > --- "Foelz.Frank" <[EMAIL PROTECTED]> wrote: > > > What I need is exactly what Oracle doesn't support. Logging "who" > > > changed "what" in a special area of our database. > > > > > > I think triggering the events will be much more specific and more > > > easy to change. > > > > > > In case all our applications use the same database and user, I am > > > trying to check out > > > what application is changing monitored tables (i.e. > > > c:\app\userapp\app.exe is changing table1). > > > What do you think of that ?? = Thomas B. Cox "Saepe in errore sed numquam in dubito" [EMAIL PROTECTED] http://www.geocities.com/tbcox23/ "The whole aim of practical politics is to keep the populace alarmed (and hence clamorous to be led to safety) by menacing it with an endless series of hobgoblins, all of them imaginary." --H.L. Mencken __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas B. Cox INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: AW: auditing tables
Silly me. I was assuming a properly designed database in which
all the PK's are generated. The PK never changes in such a database.
Jared
"Igor Neyman" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/29/02 08:12 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:Re: AW: auditing tables
> Is there some other reason for saving both in the audit table?
Jared,
You must be right.
But, if for some 'crazy' reason primary key of the record gets modified,
then you need both old and new in audit table.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, January 29, 2002 10:05 AM
> On Tuesday 29 January 2002 03:00, Rachel Carmichael wrote:
> > Update -- add two rows to the auditing table -- first with old values
> > and type = O
> > second with all the new values and type =N
> >
>
> Rachel,
>
> I don't think you need two rows for updates. The old values
> will be in the audit table, the new ones are in the production
> table.
>
> At least that's the way I've always done it.
>
> Is there some other reason for saving both in the audit table?
>
> Jared
>
> > --- "Foelz.Frank" <[EMAIL PROTECTED]> wrote:
> > > TNX for your answers.
> > >
> > > What I need is exactly what Oracle doesn't support. Logging "who"
> > > changed
> > > "what" in a
> > > special area of our database.
> > >
> > > I think triggering the events will be much more specific and more
> > > easy to
> > > change.
> > >
> > > In case all our applications use the same database and user, I am
> > > trying to
> > > check out
> > > what application is changing monitored tables (i.e.
> > > c:\app\userapp\app.exe
> > > is changing
> > > table1).
> > > What do you think of that ??
> > >
> > > greets
> > >
> > > > Frank <
> > > >
> > > >
> > > >Von: Rachel Carmichael [mailto:[EMAIL PROTECTED]]
> > > >
> > > >what sort of information are you looking to audit? if you want any
> > > >sort of detail, you are better off with triggers and possibly an
> > >
> > > audit
> > >
> > > >table. Oracle doesn't record WHAT has been changed, just that the
> > > >table was accessed. So you don't know the row etc...
> > > >
> > > >--- "Foelz.Frank" <[EMAIL PROTECTED]> wrote:
> > > >> Hi all,
> > > >>
> > > >> does anyone have experience in using Oracle's possibilities of
> > > >> auditing
> > > >> a database ??
> > > >>
> > > >> I am interested in performance questions i.e. is it a hughe loss
> > >
> > > of
> > >
> > > >> performance
> > > >> when auditing tables Inserts/Updates/Deletes. Should I use
> > >
> > > triggers
> > >
> > > >> instead
> > > >> ?
> > > >>
> > > >> any hints (comments, websites, etc...) are welcome.
> > > >>
> > > >> > Frank <
> > > >>
> > > >> --
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: Foelz.Frank
> > > INET: [EMAIL PROTECTED]
> > >
> > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> > > San Diego, California-- Public Internet access / Mailing
> > > Lists
> > >
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
> >
> > __
> > Do You Yahoo!?
> > Great stuff seeking new owners in Yahoo! Auctions!
> > http://auctions.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jared Still
> INET: [EMAIL PROTECTED]
>
> Fat City Netw
Re: AW: auditing tables
> Is there some other reason for saving both in the audit table? Jared, You must be right. But, if for some 'crazy' reason primary key of the record gets modified, then you need both old and new in audit table. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, January 29, 2002 10:05 AM > On Tuesday 29 January 2002 03:00, Rachel Carmichael wrote: > > Update -- add two rows to the auditing table -- first with old values > > and type = O > > second with all the new values and type =N > > > > Rachel, > > I don't think you need two rows for updates. The old values > will be in the audit table, the new ones are in the production > table. > > At least that's the way I've always done it. > > Is there some other reason for saving both in the audit table? > > Jared > > > --- "Foelz.Frank" <[EMAIL PROTECTED]> wrote: > > > TNX for your answers. > > > > > > What I need is exactly what Oracle doesn't support. Logging "who" > > > changed > > > "what" in a > > > special area of our database. > > > > > > I think triggering the events will be much more specific and more > > > easy to > > > change. > > > > > > In case all our applications use the same database and user, I am > > > trying to > > > check out > > > what application is changing monitored tables (i.e. > > > c:\app\userapp\app.exe > > > is changing > > > table1). > > > What do you think of that ?? > > > > > > greets > > > > > > > Frank < > > > > > > > > > > > >Von: Rachel Carmichael [mailto:[EMAIL PROTECTED]] > > > > > > > >what sort of information are you looking to audit? if you want any > > > >sort of detail, you are better off with triggers and possibly an > > > > > > audit > > > > > > >table. Oracle doesn't record WHAT has been changed, just that the > > > >table was accessed. So you don't know the row etc... > > > > > > > >--- "Foelz.Frank" <[EMAIL PROTECTED]> wrote: > > > >> Hi all, > > > >> > > > >> does anyone have experience in using Oracle's possibilities of > > > >> auditing > > > >> a database ?? > > > >> > > > >> I am interested in performance questions i.e. is it a hughe loss > > > > > > of > > > > > > >> performance > > > >> when auditing tables Inserts/Updates/Deletes. Should I use > > > > > > triggers > > > > > > >> instead > > > >> ? > > > >> > > > >> any hints (comments, websites, etc...) are welcome. > > > >> > > > >> > Frank < > > > >> > > > >> -- > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > -- > > > Author: Foelz.Frank > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > > San Diego, California-- Public Internet access / Mailing > > > Lists > > > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You may > > > also send the HELP command for other information (like subscribing). > > > > __ > > Do You Yahoo!? > > Great stuff seeking new owners in Yahoo! Auctions! > > http://auctions.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jared Still > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: AW: auditing tables
Jared, I just put the old values in the audit table with an additional column which tells exactly which columns are changed. Ah the wonders of dynamic sql ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Tuesday, January 29, 2002 10:06 AM To: Multiple recipients of list ORACLE-L Rachel, I don't think you need two rows for updates. The old values will be in the audit table, the new ones are in the production table. At least that's the way I've always done it. Is there some other reason for saving both in the audit table? Jared *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
Re: AW: auditing tables
On Tuesday 29 January 2002 03:00, Rachel Carmichael wrote: > Update -- add two rows to the auditing table -- first with old values > and type = O > second with all the new values and type =N > Rachel, I don't think you need two rows for updates. The old values will be in the audit table, the new ones are in the production table. At least that's the way I've always done it. Is there some other reason for saving both in the audit table? Jared > --- "Foelz.Frank" <[EMAIL PROTECTED]> wrote: > > TNX for your answers. > > > > What I need is exactly what Oracle doesn't support. Logging "who" > > changed > > "what" in a > > special area of our database. > > > > I think triggering the events will be much more specific and more > > easy to > > change. > > > > In case all our applications use the same database and user, I am > > trying to > > check out > > what application is changing monitored tables (i.e. > > c:\app\userapp\app.exe > > is changing > > table1). > > What do you think of that ?? > > > > greets > > > > > Frank < > > > > > > > > >Von: Rachel Carmichael [mailto:[EMAIL PROTECTED]] > > > > > >what sort of information are you looking to audit? if you want any > > >sort of detail, you are better off with triggers and possibly an > > > > audit > > > > >table. Oracle doesn't record WHAT has been changed, just that the > > >table was accessed. So you don't know the row etc... > > > > > >--- "Foelz.Frank" <[EMAIL PROTECTED]> wrote: > > >> Hi all, > > >> > > >> does anyone have experience in using Oracle's possibilities of > > >> auditing > > >> a database ?? > > >> > > >> I am interested in performance questions i.e. is it a hughe loss > > > > of > > > > >> performance > > >> when auditing tables Inserts/Updates/Deletes. Should I use > > > > triggers > > > > >> instead > > >> ? > > >> > > >> any hints (comments, websites, etc...) are welcome. > > >> > > >> > Frank < > > >> > > >> -- > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Foelz.Frank > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing > > Lists > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > __ > Do You Yahoo!? > Great stuff seeking new owners in Yahoo! Auctions! > http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: AW: auditing tables
triggers -- that do an insert into an auditing table. Been there, done that: Insert -- add a row to the auditing table of all the new values with one extra column "type" =I Delete add a row to the auditing table with all the old values and type=D Update -- add two rows to the auditing table -- first with old values and type = O second with all the new values and type =N --- "Foelz.Frank" <[EMAIL PROTECTED]> wrote: > TNX for your answers. > > What I need is exactly what Oracle doesn't support. Logging "who" > changed > "what" in a > special area of our database. > > I think triggering the events will be much more specific and more > easy to > change. > > In case all our applications use the same database and user, I am > trying to > check out > what application is changing monitored tables (i.e. > c:\app\userapp\app.exe > is changing > table1). > What do you think of that ?? > > greets > > > Frank < > > > >Von: Rachel Carmichael [mailto:[EMAIL PROTECTED]] > > > >what sort of information are you looking to audit? if you want any > >sort of detail, you are better off with triggers and possibly an > audit > >table. Oracle doesn't record WHAT has been changed, just that the > >table was accessed. So you don't know the row etc... > > > > > >--- "Foelz.Frank" <[EMAIL PROTECTED]> wrote: > >> Hi all, > >> > >> does anyone have experience in using Oracle's possibilities of > >> auditing > >> a database ?? > >> > >> I am interested in performance questions i.e. is it a hughe loss > of > >> performance > >> when auditing tables Inserts/Updates/Deletes. Should I use > triggers > >> instead > >> ? > >> > >> any hints (comments, websites, etc...) are welcome. > >> > >> > Frank < > >> -- > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Foelz.Frank > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: AW: auditing tables
"Foelz.Frank" wrote: > > TNX for your answers. > > What I need is exactly what Oracle doesn't support. Logging "who" changed > "what" in a > special area of our database. > > I think triggering the events will be much more specific and more easy to > change. > > In case all our applications use the same database and user, I am trying to > check out > what application is changing monitored tables (i.e. c:\app\userapp\app.exe > is changing > table1). > What do you think of that ?? > > greets > > > Frank < dbms_application_info + triggers. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
