Modifier spool temp space
Profile A 300G 1G
I have a question about modify temp space of profile. I try to modify a profile B's temp space to 1 G with a user(modifier) whose profile A has a temp sapce of 1 G. But I have the error 5682 :User has insufficient SPOOL space. I tried to assign 1 k temp space to profile B but always the same error.
I test to modify spool space of profile B in the limit of the modifier user's profile A. It works. For exemple, the modifier's profile A has 300 G spool, I can modify profile B's spool space to 300 G without problem.
I forget something to make the modify temp space work? The assign of spool and temp space are not the same principe?
Thanks for your reply.
Solved! Go to Solution.
Temporary space is the amount of space needed for the user to create Global Temporary Tables. Intermediate result sets, Volatile Tables, etc. are in Spool.
To understand why the 5682, insufficient spool, you need to see the SQL statement that got the error, the explain plan, and perhaps even a DBQL trace. Also note that some users can get the error and others not, for a variety of reasons, one of which might be skew. (See the SKEW parameter of Spool space in the DDL manual.)
5682 means you are out of spool. Modifying temp space does not affect spool. You need to increase spool space, not temp. The easiest clue to how much spool space you need is to examine the explain for the SQL staetment that is failing.
Both the new TEMP space limit and the (unchanged) SPOOL space limit are being validated against the limits for the user doing MODIFY PROFILE.
In other word, if I have a profileA with 400 G spool and 0 G temp, now I use userB with profileB who has 300 G spool and 1 G temp space to modify profileA. I don't want to touch profileA's spool but just temp space as:
Modify profileA as
temporary = 1 G
In any way I will get 5682 error because Teradata check the modifier spool limit which is 300 G < profileA's 400 G.
Is that right, Fred?
I think I encoutered almost the same issue with 5683.
I managed to solve the issue by droping/re-creating profile with required temp space allocation.
is there any drawback by doing so ?