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).
begin:vcard n:Jerman;Don tel;work:919.508.1886 x-mozilla-html:TRUE org:Database Management Service,Information Technology version:2.1 email;internet:[EMAIL PROTECTED] title:Database Administrator adr;quoted-printable:;;Database Management Service,Information Technology=0D=0A104 Fayetteville Street Mall;Raleigh;NC;27699-1521;USA x-mozilla-cpt:;-9536 fn:Don Jerman end:vcard
