16.20 - PERMANENT - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
English (United States)
Last Update

Number of bytes to be reserved for permanent storage for this user. The space is taken from unallocated space in the database or user of the immediate owner.

You must specify a value for this option. There is no default.

A global temporary table requires a minimum of 512 bytes from the PERM space of its containing user or database. This space is used for its table header.

You cannot specify any other options for the new user until after you specify the PERMANENT and PASSWORD options.

The only significant difference between a Teradata Database user and a database is that you can specify a password for a user, but not for a database.
You can enter the number of bytes as an integer, decimal, or floating point value or as a constant expression whose evaluation determines the number of bytes. You can also enter the value using exponential notation. For example, you can write one thousand as either 1000 or 1E3.
To create a user without database component, specify a value of 0 bytes for PERMANENT space. You must still specify a SPOOL space value for the user.
Any SQL expression that does not make any column references. Specifying an appropriate constant expression for the PERM space size of a user enables Teradata Database to assign an optimal quantity of PERM space that scales to the size of your system by allocating PERM space on a per AMP basis.
When you specify a PERM space size based on a constant expression, the assigned size does not automatically scale if, for example, you add more AMPs to your system.
Optional keyword that redundantly specifies the unit for the amount of space allowed.
Keyword that you use to specify a skew limit for PERMANENT space. You can specify a skew limit percentage that allows the maximum AMP space usage to be above the per-AMP quota, that is, the system maximum space limit divided by the number of AMPs.
Constant expression or constant from 0 through 10000. Specify a value from 1 to 9999 to indicate an AMP-level limit, which is the per-AMP quota * (1+permskewlimit/100). Specify 0 to set space accounting to the per-AMP level, that is, no skew. A value of 10000 indicates unlimited skew, up to the system maximum space limit.
Use the value of DBS Control DefaultPermSkewLimitPercent.
Optional keyword that you can include for readability to indicate that the constant_expression or DEFAULT keyword specifies a percentage of allowable skew.

Example: Using a Constant Expression to Specify the PERM, SPOOL, and TEMPORARY Space for a User

The following request creates PERM space for a user with a size based on the constant expression 2,000,000 (HASHAMP()+1), SPOOL with a size based on the same constant expression 2,000,000 (HASHAMP()+1), and TEMPORARY with a size based on the constant expression 2,000,000 (HASHAMP()+1). The expressions calculate the number of AMPs in the current system and scales the PERM, SPOOL, and TEMPORARY space for the sls120639 user to that size.

     CREATE USER sls120639 AS
       DEFAULT DATABASE = it_dev,
       PASSWORD = (EXPIRE = 0),
       PERM = 2000000*(HASHAMP()+1),
       SPOOL = 2000000*(HASHAMP()+1),
       TEMPORARY = 2000000*(HASHAMP()+1);

Example: Creating a User with a Permanent Space Skew Limit

On a system with 4 AMPs, you create a user with 1 gigabyte of permanent space. The per-AMP quota of permanent space is 250 megabytes. If you set the permanent space skew limit to 10%, any AMP is allowed a skew limit of 25 megabytes over the per-AMP quota of permanent space. An AMP can use up to 275 megabytes of permanent space as long as total permanent space used does not exceed the 1 gigabyte global limit for permanent space.
     CREATE USER user1 AS
       PASSWORD = (EXPIRE = 0), 
       PERM = 1e9 SKEW = 10 PERCENT;