Oracle-L: RE: Replication?

From: Witold Iwaniec <wiwaniec_at_novalistech.com>
Date: Thu, 15 May 2003 07:58:31 -0800
Message-ID: <F001.00599B85.20030515075831@fatcity.com>

RE: Replication?Thanks for the replies.
The resultsets can be large and include data from many tables. I thought about inserting/updating through database links because I would know which tables are involved.

I think exporting the data to the local DB will not be a problem but merging the changes is the bigger issue. For example there may be 10000 records exported to the local DB and only three of them changed. Obviously I want to merge the three records only. If I compared the local resultset with the master DB, there would be a lot of operations over the database links and probably the performance would be bad.
I could have some kind of transaction log/audit on the master DB that would store keys of the records that have changed and merge only these.

But there is huge functionality with fast algorithms already built into Oracle so I wonder if I can use something for the task.

Witold

  -----Original Message-----
  From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Paula_Stankus_at_doh.state.fl.us
  Sent: 14 May 2003 6:42 PM
  To: Multiple recipients of list ORACLE-L   Subject: RE: Replication?

Large PL/SQL operations over a database link have always seemed problemmatic. What if you were to do a CTAS unrecoverable, parallel to read the local table back into the MAIN database then processed with PL/SQL into the "MAIN" table?

Oracle OCP DBA

  -----Original Message-----
  From: Arup Nanda [mailto:orarup_at_hotmail.com]   Sent: Wednesday, May 14, 2003 4:02 PM
  To: Multiple recipients of list ORACLE-L   Subject: Re: Replication?

You could use relication but it's not absolutely necessary. You could do something like this

  On Server:
  Run a query: SELECT * FROM MAINTAB
  If they like it, issue INSERT INTO LOCALTAB_at_LOCALMACHINE SELECT * FROM   MAINTAB;
  OR From the local machine : INSERT /*+ APPEND */ INTO LOCALTAB SELECT * FROM
  MAINTAB;
  When done with the edits, you can merge with the main table. If the changes
  are exclusively done by a single user, you could

delete the records from MAINTAB
and, insert /*+ APPEND */ into MAINTAB select * from LOCALTAB_at_LOCALMACHINE. If that's not possible, use a simple PL/SQL block to read the localtab and insert/update/delete the MAINTAB.

This operation will be much simpler than replication.

HTH. Arup Nanda
www.proligence.com

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, May 14, 2003 3:11 PM

> Hello DBAs
>
> I am going to experiment with copying subset of data to another database
  and
> merging changes. In short I need the user to run a query against the
  master
> database, copy the resultset to their local database, do whatever they
  need
> and later merge the changes in their local DB to the master DB.
> It looks like replication is the technology to look into. Is there
  anything
> else in the big Oracle world?
>
> Thanks
>
> Witold
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Witold Iwaniec
> INET: wiwaniec_at_novalistech.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (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.net   --
  Author: Arup Nanda
    INET: orarup_at_hotmail.com

  Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
  San Diego, California        -- Mailing list and web hosting services
  ---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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.net
-- 
Author: Witold Iwaniec
  INET: wiwaniec_at_novalistech.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).

Received on Thu May 15 2003 - 10:58:31 CDT