Defining Temporary Space Limits - Teradata Database

Teradata Database Administration
Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
Product Category
Software

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.

Note: 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.

    Note: 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.

    Note: 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.