16.10 - Transferring Permanent Space - Teradata Database

With CREATE, GIVE, and DROP, you can transfer the permanent space of one database or user to another. This is particularly useful if you wish 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.