I'm having issues with spool space so I decided to break down my query. Even in it's simplist form I'm getting spool space errors.
My question is does allocated temp space figure into spool space?
Allocated Spool - 299,999,999,952
Allocated Temp - 48,318,382,080
Spool space is taken from the disk cylinders that are not being used for permanent or temporary data...
However the query spooling out needs to be revisited. Explain plan could probably help you out to deteminte the results that are consuming the big chunk of spool.
This is a query to find "phantom spool", i.e. a user who is not logged on to the system, but reported as having some spool in use. It's a problem in Teradata's internal housekeeping.
It will be fixed during the next database restart, but if this spool size causes problems (e.g. it reaches a double digit percentage of the user's assigned spool and the user runs out of spool) it can be fixed by the dba using the UPDATE SPACE utility.
In your case this shouldn't be a problem as you got 300GB spool and you probably didn't get a "out of spool on disk" error.
You should post your failing query + explain.
Following query will check for spool usage/skew:
100*max(currentspool)/max(coalesce(maxprofilespool, maxspool)) as PercentInUse
where DatabaseName = user;
On a User level?
SELECT TempSpace FROM dbc.UsersV WHERE UserName = USER;
Or on a system level?
do Spool and tempspaces are unused permspace ?
So only the upper limits we specify for spool and temspace for the users.
Both are shared by all users if it is available under their limits. Is my understanding correct ?