Re: a general 'Transactions in a database' question

"Mark Townsend" <markbtownsend_at_home.com> wrote in message news:B6C6CEC4.476F%markbtownsend_at_home.com...
> You didn't mention what version of Oracle, but
>
> 1) In all versions of Oracle, you could use auditing to capture and audit
> trail. This will add some overvhead, and you may find it difficult to
 track
> a particular row. See
>

 http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/
> a76965/c27audit.htm

If you just use the auditing provisions supplied by Oracle, then you definitely *won't* see the rows involved. That merely tracks the usage of privileges.

>
> 2) In Oracle8i, Logminer gives you another offline capability you may
> suffice for what you are looking for. You can track who did 'what' to what
> rows - however, you won't actually see the what (i.e DML statments), just
> the affect they had. See

Log miner most definitely does show you the SQL statements involved in DML -the the column SQL_REDO in v$logmnr_contents shows you the SQL statement issued. And SQL_UNDO shows you the logical reverse of that statement.

Now it's true that what is shown is not precisely the SQL statement AS ISSUED (ie, 'update emp set sal=10000' when issued turns into a row-by-row 'update emp set sal=10000 where rowid=x.x.x) -but it's easy to work out what statement gave rise to 3 million updates when all 3 million follow the same form and format.

Regards
HJR
>

 http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/
> a76956/archredo.htm#12680
>
>
> > From: "sdenney" <sdenney_at_healthetech.com>
> > Organization: TSCNET, Inc. (using Airnews.net!)
> > Newsgroups: comp.databases.oracle.server
> > Date: Fri, 2 Mar 2001 09:51:57 -0800
> > Subject: a general 'Transactions in a database' question
> >
> > HI all,
> >
> > How does one keep track of all transactions made in a database? In
 other
> > words, if I want to take a certain record and be able to browse back
 through
> > what has been done with it and by whom, should I create transaction
 tables
> > and triggers, or let Oracle handle it? I am looking for historical
 data,
> > rather than just the ability to recover a database.
> >
> > Does anyone have experience? Does the question make sense?
> >
> > Thanks in advance,
> >
> > SD
> >
> >
>
Received on Sat Mar 03 2001 - 18:56:04 CST