Re: ORA-1652 no matter how big temp tablespace made

taylorsi_at_mar.dfo-mpo.gc.ca (Shirley Taylor) wrote in message news:<d19ad877.0308130638.40cbf974_at_posting.google.com>...
> Hi,
> We have a query which runs on our development instance using a total
> of 512M of temp tablespace. On the production instance it fills a 6gig
> temporary tablespace and fails. Database parameters are identical.
> Tablespaces are temporary not permanent. I used dbms_stats on the
> production server and exported the stats to the development server
> where I imported them but the query still runs properly and only uses
> 512M of sort space in temp. I've sent a mass of stuff to Oracle
> support but haven't heard back yet. Anyone experience this before?
> Thanks,
> Shirley

2 words:

cartestian join.

whatever query that is causing this, does not have enough joins to limit the result set.

I'd think about setting a quota on the temp tablespace for each user. Its your (bad) sql that is being run that is the problem. you could also consider resource_limits so that this would fail long before filling the tablespace.

most likely - an error is being reported in the alert log. check the file %ORACLE_BASE%\%ORACLE_SID%\bdump\alrt%ORACLE_SID%.log

hth.

Paul Received on Wed Aug 13 2003 - 15:09:53 CDT