Regarding Temporary Space

Tools & Utilities
Junior Contributor

Re: Regarding Temporary Space

Both user A and user B have exactly the same limits, not the spool/temp space is inherited, just the numbers.
And spool is not based on the owner's perm space, it's all currently not used perm space throughout the system.

Dieter

Re: Regarding Temporary Space

i have created a temp table ABC, want to insert records in it. it is inserting only 80000 records but while selecting the insert 120000 records r getting selected. is it because of space allocation?

Enthusiast

Re: Regarding Temporary Space

Can you post the script for us to share our thoughts?

Teradata Employee

Re: Regarding Temporary Space

Simple Way Of Putting....

Spool & Temp Both Are UnUsed Perm Space Throughout The System.

Spool Table & Temp Table have Different Features.

Spool Table Occupy Spool Space, So If The Concern For Someone Is To Reduce The Spool Consumption, One Can Create Temp Table, Where The Data Is Stored In The Temporary Space, Thereby Allowing Valuable Spool Space To Be Used For Other Operations.

Also, Persistent Image Is Stored In DBC Metadata Tables For Temp Tables & They Also Survive Restart & Newer Teradata Release Allows Stats Collections Also...All These Features Are NOT Available For Spool Table/Volatile Table.

Re: Regarding Temporary Space

Dieter,

Could you please explain how the below one works?

System has 100GB space. 10GB has been allocated to user and 20GB allocated to user1. How spool is availabe?

-Rami

Enthusiast

Re: Regarding Temporary Space

If you allocate 10GB of perm space to the user that means they can use up to 10 GB of space for storing data on your system .

Spool is different, it does not pre-allocate space for the user. It is a thresholdlimit  that the user is allowed to use if there is available free space on the system. Available space comes from unused perm and is temporary in nature. That means that the spool is only used for the query that is running and then released as it is no longer needed.

If you have a lot of users running queries with the same spool limit , they could use up all the available spool and your current user may not be able to get the 20GB requested, possibly causing the job to fail.

This makes it important to understand what the users will be running when you allocate spool to them.

--Shelley--