Re: Oracle Transaction Boundary ..

From: Lewis C <lewisc_at_excite.com>
Date: Wed, 04 May 2005 09:59:45 GMT
Message-ID: <tr6h719j31g9f3f6jsee4sctasto96du95@4ax.com>

On 4 May 2005 01:41:47 -0700, "Raghavendra S. Kothamangala" <raghavendra.kothamangala_at_gmail.com> wrote:

>Hi,
>
>I have a requirement to do custom processing for any new
>transaction, which happens in a Oracle database.
>
>When there is a new transaction in the oracle database, how can
>I know the following information.
>
>1. The tables involved (which were updated) in the transaction.
>2. The sequence of the updates.
>3. The source (user) which updated the transaction.
>4. The start and commit time.
>
>I have to do processing on this data immediately, and update
>another database.
>
>What is the simplest way to achieve this?
>
>Suggestions and Pointers are appreciated.
>
>Thanks in Advance
>-Raghu

Not enough info to really understand your requirement, but I can take a few guesses.

I'm going to guess 10g and that you're trying to do something behind the scenes of a third party application that you can't change. I'm guessing 10g because I like it and third party app because what you want to do would best be accomplished by changing your application.

Anyway, I see a few different ways to do it:

Triggers on all the tables to catch the changes and timestamps. Lot of code, some performance impact, not the simplest. Not 10g specific.

Audit. Everything. Everyone. Have a monitor job watch the audit views (or aud$). Fairly simple. Make sure to keep aud$ clean. Not 10g specific.

Possibly, expression filters. Filters will monitor what's coming in based on expressions (i.e. rules). I haven't tested it. It may not be what you want. It is 10g specific but might be the simplest.

Those are three things off the top of my head. Hope that helps.

Lewis

Lewis R Cunningham

Author, ItToolBox Blog: An Expert's Guide to Oracle http://blogs.ittoolbox.com/oracle/guide/

Topic Editor, Suite101.com: Oracle Database http://www.suite101.com/welcome.cfm/oracle

Received on Wed May 04 2005 - 04:59:45 CDT