Help!!! Unexpected ORA-1652 error

OS: HP-UX 10.2, Oracle RDBMS : 7.3.3.5.1, No Parallel Query

We encountered a strange problem with one of our batch jobs. This batch job is basically consisting of SQl queries with multiple table joins followed by inserts. When this script ran we received an ORA-1652 within a minute of the script running. The database parameters are:

                     DB_BLOCK_SIZE = 4K
                     Sort Area size: 256000
                     TEMP tablespace size: 70M
                     TEMP tablespace initial: 260K
                     TEMP tablespace next: 256K

We checked the status of the TEMP tablespace through Oracle Tablespace Manager, and it showed that the entire TEMP tablespace was occupied. This was the only active session running against the database at that time. However the number of Disk sorts for that session got from V$SESSTAT showed 0.

We next increased the parameters to following and again ran the script with the same result as above and again no sort activity to disk from v$sesstat:

                     sort area size: 256000
                     TEMP tablespace size: 400M
                     TEMP tablespace initial: 260K
                     TEMP tablespace next: 1024K

We next ran the script with the following settings, and it ran successfully:

                     sort area size: 64000
                     TEMP tablespace size: 400M
                     TEMP tablespace initial: 128K
                     TEMP tablespace next: 128K

This time again V$sesstat showed no disk sort activity.

This behaviour is really perplexing me and goes against the described behaviour of Oracle RDBMS regarding its use of TEMP tablespace for sorting. Could I have hit the proverbial Oracle bug. Could any of any of you Oracle Gurus provide any explanation.

Thanks
SSEN -----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Oct 01 1998 - 16:03:08 CDT