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