Regarding Temporary Space

Tools
Enthusiast

Regarding Temporary Space

Hi Friend

Help me to solve the following question

Which 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
15 REPLIES
Enthusiast

Re: Regarding Temporary Space

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.

Enthusiast

Re: Regarding Temporary Space

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

Re: Regarding Temporary Space

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

Re: Regarding Temporary Space

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

Re: Regarding Temporary Space

Addendum:
Temporary space is allocated before spool space.
Both are derived from existing free space.
If unspecified, default is set to max of immediates owner.
http://www.teradatau.courses.teradata.com/learning/BLADE_MS/legacy/29956_SQL_Adv/13TempTbls/wbt-sql160c0.htm
Junior Contributor

Re: Regarding Temporary Space

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

Dieter
Enthusiast

Re: Regarding Temporary Space

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.
Teradata Employee

Re: Regarding Temporary Space

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

Re: Regarding Temporary Space

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?