All Forums Tools
kiran_terydata 13 posts Joined 09/09
01 Feb 2010
Regarding Temporary Space

Hi FriendHelp me to solve the following questionWhich of the following is a statement that is true regarding temporary space? A. Temporary space is spool space currently not used. B. Temporary space is permanent space currently not used. C. Temporary space is subtracted from SysAdmin. D. Temporary space is assigned at the table level. Thanks & Regards KIRAN K

Jimm 298 posts Joined 09/07
01 Feb 2010

Temporary space is the amount of space which can be used to create temporary tables - either volatile tables or global temporary tables.The space comes from within the users spoolspace; it is in effect a subset of spoolspace. When you create a user, you specify (explicity or by inheritance) the maximum spoolspace the user can use and how much of this can be used for volatile tables.All space is managed at database level. For volatile tables, the space has to be within the users database - ie if my username is jimm, I can only create volatile tables as jimm.tblname. Global temp tables are referred to by the database containing the definition eg GTTDB.tblname. However, when I reference GTTDB.tblname (usually by an insert initially) an instance of this table is created in my tempspace. Hence, if 2 users (or even 2 sessions) refer to GTTDB.tblname, they get separate instances with separate contents.

Jim Chapman 449 posts Joined 09/04
01 Feb 2010

That's not completely correct.Volatile tables use spool space. Global temporary tables use temporary space. The quota of temporary space available to a user is separate from perm space and spool space.

Darin 13 posts Joined 09/05
31 Aug 2010

Teradata's spool and temporary space space is perm space that is not currently being used.

rupert160 131 posts Joined 09/10
21 Jan 2011

There is not a lot of information about the reason behind why this "Temporary Space" allocation exists. All I have found out is:
Temporary space is a Teradata "Extended Attribute" reserved prior to spool space for any user defined with this characteristic.
Temporary space is a new and distinct allocation with separate accounting to the Spool Space allocation.

I'd love somebody to elaborate as to what the goals and reasons for it's existence and mechanics of it's use. So far I can see no reason for why spool space couldn't have done all that it does already...

rupert160 131 posts Joined 09/10
21 Jan 2011

Temporary space is allocated before spool space.
Both are derived from existing free space.
If unspecified, default is set to max of immediates owner.

dnoeth 4628 posts Joined 11/04
22 Jan 2011

The main difference between Spool and Temp is the behaviour during a restart:
When Teradata crashes, Spool is discarded, but Temp is recovered.
Clients can reconnect the previous session within a certain timeframe (20 minutes by default).
When the session reconnects, only Volatile Tables are lost, but not the session's Global Temp Tables (of course when the NO LOG option is set, the table is empty).



Jim Chapman 449 posts Joined 09/04
24 Jan 2011

To elaborate on the goals and reasons, we introduced temp space along with the global temporary tables feature. Its sole purpose is to support temporary tables. Since temporary tables have a different persistence characteristic than either permament or spool/volatile tables, it seemed that administrators would want to set different usage quotas. Therefore, we extended the disk space accounting system to track temporary space usage separately.

ana.sipaque 3 posts Joined 02/11
08 Apr 2011

Hi. I hope this help

Perm Space: The maximum amount of Permanent Space assigned and available to a User or Database to store tables. Unlike some other relational databases, the Teradata Database does not physically pre-allocate Perm Space for Databases and Users when they are defined during object definition time. Only the Permanent Space limit is defined, then the space is consumed dynamically as needed. All Databases have a defined upper limit of Permanent Space.

Spool Space: The amount of space assigned and available to a User or Database to gather answer sets. For example, when executing a conditional query, qualifying rows are temporarily stored using Spool Space. Depending on how the system is set up, a single query could temporarily use all available system space to store its result in spool. Permanent Space not being used for tables is available for Spool Space.

Temp Space: The amount of space used for global temporary tables, and these results remain available to the User until the session is terminated. Tables created in Temp Space will survive a restart. Permanent Space not being used for tables is available for Temp Space as well as Spool Space.

According to this. The answer should be:

Which of the following is a statement that is true regarding temporary space?

B. Temporary space is permanent space currently not used

TDlearner 2 posts Joined 09/10
19 Apr 2011

Hi..Could somebody explain me on the below given situation?

DBC has two users USER A and USER B. Both have inherited the max temp space and spool space limits (for example 50,000,000 bytes each) from the parent i.e, DBC. In a given situation if USER A has utilized 50,000,000 bytes of both temp and spool space, can USER B use the unused perm space of it as spool space what are the limitations on USER B at that particular point?

dnoeth 4628 posts Joined 11/04
19 Apr 2011

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.



adijagadish 1 post Joined 03/12
13 Mar 2012

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?

Stefans 38 posts Joined 02/12
13 Mar 2012

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


SmarakDas 51 posts Joined 02/12
26 Mar 2013

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.

rams2all 1 post Joined 01/12
04 Sep 2013

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?

Shelley 28 posts Joined 09/10
05 Sep 2013

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.

You must sign in to leave a comment.