Massive update troubles

I have a data conversion team working on our financial data, prepping it for load into SAP. My concept for the conversion process was to download the flat files, run programs written in C or Perl to transform the data, then use SQL*Loader to load them into relational tables for constraint testing and general analysis, prior to export to the SAP system.

At some point early on I lost control, and this mutated into: download the flat files, load them into Oracle, run stored procedures to transform the data through 2 or 3 stages to a new schema for SAP. Still ok, you're thinking.

But -- now we're running more than a couple hundred thousand rows at the time, and the developers are still tweaking the process
(because the SAP requirements are slowly mutating -- another
issue). Frequently the developers will stop a long running query with ALTER SESSION KILL -- this is working but often takes a very long time to roll back. Well, there's the rub -- this is a PC system, and the developers frequently want to make a tweak to their program and re-run it. This puts a tremendous load on what the PC isn't good at -- I/O.

So I wind up with a frantic developer on the phone "the Oracle server's locked up!" and sure enough, he's right -- there's so much going on in there you can't do anything that requires a disk access..... The evolved response is shutdown abort, startup mount, recover, open. This always works and always takes about 3 minutes. Naturally, I've moved them to their Very Own server, so that this doesn't disrupt other work.

Can anyone turn their diagonstic eyes on this situation and suggest a better method for me to either limit the damage or recover from the problem? Or even a good method for analyzing the problem, given that we haven't the downtime to wait for all processes to complete (once in this state, a weekend can pass without successfully ending whatever the database is doing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Don Jerman
  INET: djerman_at_dot.state.nc.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).



text/x-vcard attachment: Card for Don Jerman