About Types of Space | Teradata Vantage - About Types of Space - Advanced SQL Engine - Teradata Database

Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
upb1600054424724.ditamap
dita:ditavalPath
upb1600054424724.ditaval
dita:id
B035-1093
lifecycle
previous
Product Category
Teradata Vantageā„¢

Space (whether permanent, spool, or temporary) is measured in bytes. The following table lists the three types of database space.

Type of Space Description
Permanent (PERM) Permanent space allocated to a user or database is a repository for database objects, for example, tables, indexes, and journals. Perm space can be defined as a fixed quantity or as a constant expression. 

When an object is created or data rows are inserted, the system allocates space as needed from the PERM space of the immediate owner. The space is returned automatically when no longer needed.

Perm space allocated to a user or database that is currently unused is available for use as spool or temporary space.

A database or user with no PERM space can still own views, macros, and triggers but cannot have objects that require space such as tables, UDFs, stored procedures, HIs, JIs, or journals.

Spool Spool space holds intermediate query results or formatted answer sets to queries and volatile tables. The system can use unassigned PERM space for spool space.

When a user creates new users or databases, the amount of spool space for those new objects must not exceed the spool space limit of their immediate owner. If you do not specify a spool space limit, a new user automatically inherits the spool limit of its parent.

To more easily manage spool space, define the value for spool in a profile. You can then assign a profile to users and all of the users will inherit the spool space definition.

Temporary (TEMP) Temp space defines the number of bytes the system will use to store data for global temporary tables. The value you specify must not exceed the value of the immediate parent at the time of creation. If you do not specify a value, the maximum value defaults to that of the parent.
Global temporary tables require both PERM and TEMP space. The database/user must have adequate perm space to accommodate the global temporary table header on each AMP. Table header size varies by table definition and the maximum size for a table header is 1 MB. Temp space is required for storing the actual rows.

To more easily manage TEMP space, define the value for it in a profile. You can then assign a group of users to this profile and all of the users will inherit the TEMP space definition.

You can manage space using Teradata SQL, or with Teradata Viewpoint administrative tools.