RE: Replication?

Witold - You don't say how much data is involved, but have you considered transportable tablespaces?

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Thursday, May 15, 2003 10:59 AM
To: Multiple recipients of list ORACLE-L

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-----
Paula_Stankus_at_doh.state.fl.us
Sent: 14 May 2003 6:42 PM
To: Multiple recipients of list ORACLE-L

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-----
Sent: Wednesday, May 14, 2003 4:02 PM
To: Multiple recipients of list ORACLE-L

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
<http://www.orafaq.net>
> --
> Author: Witold Iwaniec
> INET: wiwaniec_at_novalistech.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
<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
<http://www.orafaq.net>  
-- 
Author: Arup Nanda 
  INET: orarup_at_hotmail.com 

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
<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: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.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).