Re: merge command

From: Richard Stevenson <rstevenson_at_cobblesoft.com>
Date: Thu, 6 May 2004 17:14:12 -0400
Message-ID: <002a01c433af$150ec780$0201a8c0@Cobble1>

Rachel,
I second the matviews option. We've used them extensively to populate sub-sets of a production meta data repository to a data warehouse and partner database. Along with package-wrapped auto-updates (via dbms_jobs/dbms_snapshot/refresh), they work like a charm. This, BTW, is in 8.1.7. Also, if the source db goes down, the job resubs itself - or of course you can handle it programmatically to alert.

Richard.

Richard J Stevenson
CobbleSoft International Ltd.
www.cobblesoft.com
US/Can Toll-Free: 1-866-380-6716
International: +1 315 548 5810

Original Message ----- From: Goulet, Dick To: oracle-l_at_freelists.org Sent: May 06, 2004 14:27 Subject: RE: merge command

Rachel,

Why not use a materialized view??

  Dick Goulet
  Senior Oracle DBA
  Oracle Certified 8i DBA

-----Original Message-----

  From: Rachel Carmichael [mailto:wisernet100_at_yahoo.com]   Sent: Thursday, May 06, 2004 2:02 PM
  To: freelists oracle-l
  Subject: merge command

I'm testing an update process using MERGE. For reasons too complicated to go into (can you say "corporate politics"?) I have a login app in one database and the "real" admin users tables in another database. The users don't want to use database links to read the admin table real-time, they worry about the possibility of one database being down for maintenance and hanging up the second app. So I've got copies of the real tables in the login database.

Clear as mud so far?

Now I have to update the tables in the login database with the real data on a regular basis. I *could* set up replication, but these are tiny tiny TINY tables (I might have 34 rows in the largest one) and it's just not worth the effort.

So I thought, use a database link to get the real data and use MERGE to get the data in. I get to play with a new command I've never used and pretend to be a real 9i DBA. First run through, so it should all be the INSERT part of the MERGE command.

Works like a charm on the 5 row table.

I keep getting "end of file on communication channel" on the 34 row table. This happens within 5 seconds of hitting enter.

9.2.0.2 on Sun Solaris. For testing purposes, I'm actually linking back into the same database, different schema, via a dblink.=20 =20
I've looked at Metalink, the docs and even a bit into Tom Kyte's site (thanks Paul).... nothing.

Any suggestions on where I should look next?

I *could* just truncate and reload but the users are paranoid about the truncate happening and then not being able to reload and being left with no admin data

Rachel

=09
=09

Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs =20 http://hotjobs.sweepstakes.yahoo.com/careermakeover=20

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

  FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


  -----------------------------------------------------------------

  ----------------------------------------------------------------

  Please see the official ORACLE-L FAQ: http://www.orafaq.com


  ----------------------------------------------------------------

  To unsubscribe send email to:  oracle-l-request_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu May 06 2004 - 16:09:58 CDT