I read "unused Perm space is spool space" in one document.
My question was when we get "No spool space" error while processing the query ,if we drop any actaul not used tables(not temporary),is it solve the issue?
I mean is this perm space available as spool space for query processing?
Yes, spool space is taken from the available perm space but each user has a spool space limit assigned to avoid "run-away" queries which would otherwise use up the complete space.
Further, "no spool space" can be related to a certain AMp during query execution (skewed spool situation). You have to check this as well.
Dropping tables will not help in that case as all the currently unused perm (i.e. in all databases/users) is available as spool.
When you get a "2646 No more spool space" this indicates your user has hit his personal spool limit assigned by the DBA.
Only when the system runs low on spool globally ("2507 Out of spool space on disk") freeing perm will help, but then this is the DBA's problem.
I am almost clear but just need to clarify myself with below example.
Today, I installed Teradata Express and I know that I can't perform any operation unless I create an USER/DB from DBC and provide all the required access to data. Scenario is, I have 2 AMPs. I have run below queries to know Perm space and spool space allocated to DBC.
CAST(SUM(MAXPERM)/(1024*1024*1024) AS DECIMAL(7,2)) MAX_PERM,
CAST(SUM(CURRENTPERM)/(1024*1024*1024) AS DECIMAL(7,2)) CURRENT_PERM,
CAST(SUM(MAXSPOOL)/(1024*1024*1024) AS DECIMAL(7,2)) MAX_SPOOL,
CAST(SUM(CURRENTSPOOL)/(1024*1024*1024) AS DECIMAL(7,2)) CURRENT_SPOOL
WHERE DATABASENAME = 'DBC'
Now, If I create a USER with 10GB of perm and don't allocate any spool, in that case how much spool limit it is going to get by default? Is it 35.73 - 10 i.e. 25.73? or by default it is same as its parent DBC i.e. 36.89?
Let's say, I create a USER with 10GB of perm and allocate 10 GB of spool limit. In such a case, if one of my SQL is taking 20GB of spool, will it fail as spool required is exceeding limit? But ideally my SQL should be allowed the whole unused space of 25.73 isn't it?
Your response is going to clear a long time wrong conception of mine may be and hence, I will be waiting for your reply,
Thanks in advance. - Dipanjan
#1: If you don't assign spool to a new user it's inherited from the owner, in your case from dbc, 36.89 GB. You can easily check that when you create the user.
#2: When you hit your spool limit of 10 GB your query will fail, that's why there is a limitation, it's not to annoy end users, it's to protect the system.
So, as I guess the limitation is to accomodate all users with a common space which will be properly distributed for an optimum performance of all the users created in Teradata.
Thank you so much :)