Re: Help!!! Unexpected ORA-1652 error

Have you checked the temporary tablespace assignment of the user running the queries? Make sure the user is set to use the TEMP tablespace. If not, issue alter user <username> temporary_tablespace TEMP;

In article <6v0qmc$nd3$1_at_nnrp1.dejanews.com>,   suvamoysen_at_my-dejanews.com wrote:
> 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
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Oct 02 1998 - 11:06:32 CDT