| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Data load ideas
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.
 |
 |