Re: reg. problem with Temp Tablespace

On Dec 29, 8:45 am, DB Dule <DBdude_From..._at_yahoo.com> wrote:
> On Dec 29, 2:13 am, calms <calmsqur..._at_gmail.com> wrote:
>
> > Hi,
>
> > Currently im using 17gb for temp tablespace.
> > fyi, mine is oracle datawarehouse with 1.5 TB of data.
> > I increased the temp tablespace with 8gb of space. but again im  getting the same error.
>
> how is the explain plan of currently running queries? Either a query
> is processing huge number of rows in DW env or query is  doing
> cartesian join. Have you turned on start transformation since this is
> DW?
> I have seen cartesian product query was chewing up 8 gig of Temp space
> that was fired against a 800 MB schema tables.

Hash joins can also eat up a lot of temp space. When the problem job is running look at v$sort_usage to see what the temporary space is being used for. With a 1.5T database the fact may be that a 17G temporary tablespace is just no where near large enough to handle the requirements.

Is your current temporary tablespace large enough to rebuld the largest index in the database? Will you ever need to do this? Ask the same questions for the largest bitmap index?

HTH -- Mark D Powell -- Received on Sat Dec 29 2007 - 10:01:37 CST