Re: This small query kills oracle 9.2.0.3 (nightmare)

On oracle 9.2.0.3 and below it display "corrupt block" error message. No error code just it says that Oracle encountered corrupt block. From here oracle advised us to have a full check up on the server. This is what we did and in addition we changed everything except the CPUs and the RAM. Finally I was fed up after 4 weeks of searching I sit down in front of the computer and waiting 2 hours and observed what was going on. Then I noticed that slowly dbms_stats ate up all the memory (2GB). After oracle encounters the corrupt blocks (when no memory is available) it gives back to the OS the memory. However the entire instance remains in corrupted state and keeps on running !! No new connections are possible but old connections continues to work !! Real mess. I spent a day to isolate the faulty query that dbms_stats was running. This is how I got to the small query.

Oracle 9.2.0.4 detects the memory consumption and when the instance reaches the OS limit it kills the faulty sessions. Everything is back to normal after the session has been killed. However Oracle told me that the memory consumption is normal. I never managed to understand why. They told us that sort_area_size is depreciated from 9.2 !? So we have to use pga_aggregate_size.

Andras Received on Wed Sep 10 2003 - 07:13:20 CDT