Data load ideas
- From: Paul Baumgartel <treegarden@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 28 Apr 2004 12:26:11 -0700 (PDT)
We are running Oracle 9.2.0.4. We are an application service provider,
and our database contains information on all of our customers.
Currently, when we move a new customer from staging to production we:
1. Export the new customer's data (using QUERY=) from staging
2. Disable all foreign keys in production
3. Disable all triggers in production
4. Import into production
5. Enable FKs
6. Enable triggers
We have to take the site down for maintenance to do this, due to the FK
and trigger manipulation. I want to replace this procedure with a
better one.
Our tables are partitioned by company. I wanted to use partition
exchange (load new company data into a "shadow" non-partitioned table,
then exchange into new partition), but AFAIK there can be no
referencing FKs against either table's PK, so that's out. I've also
considered transportable tablespaces, but we employ multi-master
replication on production for redundancy, and transportable tablespaces
are not supported in that case.
I'm committed to eliminating export and import from the procedure, as
they bring too many restrictions with them (no control over commits,
etc.). At worst, I guess we can dump to a flat file and load from
there, using deferrable constraints (with one commit at the end of the
procedure), and triggers with a condition that causes them not to fire
if we're doing a data load. My question: is there another (easier!)
way to go, given our setup?
TIA.
=====
Paul Baumgartel
Transcentive, Inc.
www.transcentive.com
__________________________________
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs
http://hotjobs.sweepstakes.yahoo.com/careermakeover
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
