No more spool space scenarios

1. System ran out of spool space

This is the most rare situation, forget about.
There are too few free space on the system, but this situation used to be avoided by defining a "SpoolReserve" database, where no objects are created, this way that area is always available for spool.
If many "big spool limit" users run high spool queries parallel, then this rare situation can yet occured.

2. Multiple session of the user are active together
This is a quite rare situation also. Check the active users from dbc.sessioninfo.

3. Volatile tables
All existing volatile tables reside in your available spool space, reducing the available.
If you create many, and even with skewed distribution, you can stuff your spool up.
Choose "primary index" carefully, when defining volatile tables also.

4. Improper execution plan
These are the >90% of cases that cause the "No more spool space" errors. Let' see how:

(a)"Duplication to all AMPs" of a non-small set of records
    The root cause is typically missing or stale statistics. Either system thinks that much less records will be duplicated than the real (sometimes billions of records get in this kind of spools), or knows this exactly, but on the other branch of the query there are more low quality estimations, and this execution seems to be cheaper.
(b) Redistribution of records by a hash that causes skewed distribution
    Check the corresponding blog post: Accelerate skewed joins
(c) Retrieve huge amount of records into spool (locally or redistributed onto the AMPs)
    Specific query structures imply this execution, like: join to a view that "union all"-s big tables.

Increasing the spool limit will not solve the problems in the most cases.

Too big task
Sometimes a given SQL query requires big spool area to be performed, even with the best execution plan.
This is the only case when raising spool limit is the solution.