I am working with a ~100 GB datawarehouse. Our largest segment is about 5GB (excluding indexes) and we are having problems running out of temporary tablespace with our batch queries and transformations. Our temporary tablespace is sized at 10GB, and our queries often run very close using all this.
Does anyone have any guidelines for Temporary tablespace sizings? Does 10GB seem to little?
I would say that 10Gb seems a bit small, but that totaly depends of the design of your warehouse/marts, the queries executed against the db and the available hash/sort or PGA memory. So I dont think that its possible, with the given information to say if yes or no 10Gb are to small.
If you are using 9i and have set WORKAREA_SIZE_POLICY to AUTO you may have a look at the following document: