Define Temporary Space Limits | Teradata Vantage - About Defining Temporary Space Limits - Advanced SQL Engine - Teradata Database

Database Administration
Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1093
lifecycle
previous
Product Category
Teradata Vantage™

While some amount of permanent space is used to store the definition of a global temporary table, temporary space is used to hold rows of materialized global temporary tables. Temporary space is allocated at the database or user level, but not the table level.

Define a temporary space limit with the TEMPORARY parameter of a CREATE/ MODIFY PROFILE or CREATE/MODIFY USER/DATABASE statement.

A profile definition overrides any user definition, it does not append settings to the definition.

The maximum and default limits for temporary allocation are determined as described in the following table.

IF you … AND a profile… THEN the limit is inherited from the...
specify TEMPORARY in a CREATE/MODIFY USER/DATABASE statement does not apply immediate owner and may not exceed the limit of the immediate owner.
applies user who submitted the CREATE/MODIFY PROFILE statement. The limit may not exceed the limit of that user. The limit is determined as follows:
  • If that user has a profile, the system uses the limit in the profile.
  • If a profile does not apply to that user, the system uses the limit in the CREATE/MODIFY USER statement for that user.
  • If no TEMPORARY is defined for that user, the system uses the limit of the immediate owner.
do not specify a TEMPORARY limit in a CREATE/MODIFY USER/ DATABASE statement does not apply immediate owner of the user.
applies profile specification.
applies but the SPOOL parameter is NULL or NONE specification for the user who submitted the CREATE/MODIFY PROFILE statement, determined as follows:
  • If that user has a profile, the profile specification.
  • If a profile does not apply to that user, the specification in the CREATE/MODIFY USER statement for that user.
  • If no TEMPORARY is defined for that user, the limit of the immediate owning database or user.

When using the CREATE USER, CREATE DATABASE, or CREATE PROFILE statements to assign temporary space limits, keep your space limits in mind. Query the DBC.DiskSpaceV view to find the system levels for temporary space.

The following table describes the different types of temporary space.

Temporary Space Level Description
CURRENTTEMP This is the amount of space currently in use by Global Temporary Tables.
This does not include the amount of permanent space required for the table header stored on each AMP for the base global temporary table definition.
PEAKTEMP This is the maximum temporary space used since the last session.

Temporary space is released when the session terminates.
MAXTEMP MaxTemp specifies the limit of space available for global temporary table rows.
This does not include the amount of permanent space required for the table header stored on each AMP for the base global temporary table definition.

The value may not exceed the limit of the:

  • Creator or modifier of the profile, when setting TEMPORARY in a profile
  • Immediate owner of the user being created or modified, if a profile does not apply

If you do not specify a value and the user is associated with a profile, MaxTemp defaults to the value of the profile, if defined. If the profile TEMPORARY is set to NULL or NONE, or the user is not associated with a profile, MaxTemp defaults to the value of the parent of the user.