Re: Data load ideas

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 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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: