Re: How to get SQL commands executed on Oracle database in certain period?

On Oct 5, 7:55 am, "hpuxrac" <johnbhur..._at_sbcglobal.net> wrote:
> qazmlp1..._at_rediffmail.com wrote:
> > Is it possible to get the SQL commands executed on a Oracle database
> > from a certain time?
>
> > We have a system connected with Server-1 and Server-2. Server-1 uses
> > Oracle RDBMS and Server-2 uses some other database for Data storage.
> > In regular intervals, the application running on the System should
> > fetch all the SQL commands executed on Server-1 from the last interval,
> > convert if required and then apply these changes on the Server-2. I
> > cannot use redo logs, as I have to apply the changes on a non-Oracle
> > database. For this reason, I would like to know how exactly I can get
> > the SQL commands executed on a Oracle database from a certain time(or
> > in certain time period).Well using a scheduling system of some sort is probably your answer.
> On unix/linux cron is provided which is a barebones type of scheduler.
> Many unix/linux shops use other 3rd party schedulers.
>
> Windows also has a built in scheduling facility that's pretty basic.
>
> You do realize that the SQL commands used against oracle will not
> probably match exactly SQL that other databases would require don't
> you?
>
> It sounds like you will need to build something custom that will look
> at the data changes in oracle and then extract and build somehow a flat
> file that would contain the sql commands that you want executed on the
> other database.
>
> Is that a little kludgy? Maybe but not unlike things that people have
> put in place.
>
> Perhaps a better solution would be changes so that the applications
> that run against the other database would access the relevant
> information in oracle when they need that information.

Oracle does not save all DML operations anywhere from which they could be readily fetched and then applied to another non-Oracle database. You would have to mine the archived redo logs for this information, capture it via table triggers, or perhaps capture it using advanced auditing features. Depending on the number of tables involved, data dependencies, and the activity level this could be a major undertaking.

IMHO -- Mark D Powell -- Received on Thu Oct 05 2006 - 09:15:02 CDT