5682 error when modify temp space of profile

Database
Enthusiast

5682 error when modify temp space of profile

Hi,

 

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.


Accepted Solutions
Teradata Employee

Re: 5682 error when modify temp space of profile

Correct.

You will get the error because the resulting Spool limit value for Profile A, although unchanged, would be larger than that of the user issuing the MODIFY.

1 ACCEPTED SOLUTION
10 REPLIES
Teradata Employee

Re: 5682 error when modify temp space of profile

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.

Enthusiast

Re: 5682 error when modify temp space of profile

thanks for the info. I want to know why I has 5682 error.
Teradata Employee

Re: 5682 error when modify temp space of profile

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.)

Enthusiast

Re: 5682 error when modify temp space of profile

Please explain the reason in this context:modify the profile temp space. There is nothing to see with skew.
Teradata Employee

Re: 5682 error when modify temp space of profile

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.

Highlighted
Teradata Employee

Re: 5682 error when modify temp space of profile

Both the new TEMP space limit and the (unchanged) SPOOL space limit are being validated against the limits for the user doing MODIFY PROFILE.

Enthusiast

Re: 5682 error when modify temp space of 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?

Teradata Employee

Re: 5682 error when modify temp space of profile

Correct.

You will get the error because the resulting Spool limit value for Profile A, although unchanged, would be larger than that of the user issuing the MODIFY.

Teradata Employee

Re: 5682 error when modify temp space of profile

hi,

 

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 ?

 

thank you