Transfer Permanent Space | Teradata Vantage - 17.00 - 17.05 - Transferring Permanent Space - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval

With CREATE, GIVE, and DROP, you can transfer the permanent space of one database or user to another. This is particularly useful if you want to transfer permanent space from a child of a child back to user DBC when user DBC is not the immediate owner. For information on dropping databases or users, see Dropping a Database or User. For information on transferring ownership of a database or user with GIVE, see Transferring Ownership with GIVE.

For example, assume the following hierarchy:



Also, assume that:
  • F has a MAXPERM of 10, a MAXSPOOL of 50, and a MAXTEMP of 25.
  • E has a MAXPERM of 10, a MAXSPOOL of 20, and a MAXTEMP of 15.

To increase permanent space for E:

  1. From space owned by F, create temporary database X with a MAXPERM of 5:
    CREATE DATABASE X FROM F AS PERM = 5 ;

    The default is to allocate to a new database the same spool and temporary space as its owning database, so MAXSPOOL for X defaults to 50 and MAXTEMP for X defaults to 25. The PERM allocation for X is taken from the space of its owning database; thus, the MAXPERM of F is reduced to 5.

  2. Give X to E by using the GIVE statement to transfer ownership:
    GIVE X TO E;
  3. Drop X with the following statement:
    DROP DATABASE X;

    This increases the MAXPERM of E to 15.

    The MAXSPOOL and MAXTEMP of E are unchanged at 20 and 15, respectively.