How to speed up after UNDO creation maxed out Diskspace
Posted on 2007-10-19
We are running Oracle 9i on a Windows 2000 box with 4 gigs of RAM.
Drive C: is for the operating system and has 68G.
Drive D: is for Oracle and some tablespaces and has 68G
Drive G: is solely for tablespaces and has 280G.
We do not do transactions, so all logging is turned off.
We just got drive G: and installed it. SOme of the tablespaces were moved to the new drive but their physical files were still on Drive D:
Yesterday, someone utilized a temp Schema on Drive D: to run a very long query on 6 million records.
Another user did a long query on 6 million records on one of the permanent Schemas on D:
The temp space grew to 12.5G and a file called UNDOTBS1 grew to 12.5g - this, long with the tablespaces, took up the entire 68G on D:
This crashed the server - oracle disconnected since it couldnt find any more media.
we rebooted. and were able to drop an old tablespace and delete the data file from D:
I am planing on moving the TEMP space to the larger drive.
Problem is it is still slow.
Any ideas on how to speed it up?
How can I minimize or get rid of the UNDOTBS1?