Re: Data load ideas
- From: Jared Still <jkstill@xxxxxxxxxx>
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 28 Apr 2004 13:30:08 -0700
Paul,
I don't get it. Without a better understanding of your app
it makes it rather difficult.
For instance, why are there FK constraints that need to be
disabled during a load?
If the tables are loaded in the correct order, and load
script via SQL*Loader, PL/SQL, Perl or whatever you use
should be able to handle this.
I've not worked at an ASP, but have developed on a system
where many customers data had to be loaded on a daily basis.
What is preventing a procedural process with PL/SQL batch
loading from being used?
Jared
On Wed, 2004-04-28 at 12:26, Paul Baumgartel wrote:
> 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
> -----------------------------------------------------------------
----------------------------------------------------------------
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:
