Teradata Space

There are mainly three types of spaces in Teradata. These are:

1. Permanent Space.
2. Spool Space.
3. Temporary Space.

Let us understand in brief about them.

PERMANENT SPACE (Perm Space):-

This is the space where objects such as databases, users, tables, views etc are created and stored. Permanent space is released when data is deleted or when objects are dropped from the database. Permanent space defines the upper limit of space for a user or database. Suppose you have a user with access to Teradata. When you create any table, store procedure etc the space occupied will be from the Perm space. Perm space allocated to a user or a database that is currently unused is available for use as spool or temporary space. Suppose we have a Teradata system having 400GB of perm space. Initially DBC owns the 100% of the perm space of a new system. As in Teradata everything is parallel, perm space will be distributed evenly across the all AMPs. Teradata system having 400GB perm space and 4 AMPs, each AMP will get 100GB of perm space to execute query in parallel. If DBC creates two users as sysdba and appdba with 20GB and 100GB perm space respectively, space will be taken from parent i.e. here DBC. So now DBC has 280GB of perm space.

SPOOL SPACE in Teradata:-

This space is literally unused permanent space which is used to store the intermediate result of the query fired by the end user. Whenever an end user runs a query, the output temporarily stored in some space is the spool space. This should not exceed the limit of spool space provided as the query aborts immediately and you get a spool out issue. If the spool space is not specified for the user/database, it inherits the space from its parent. Spool space is taken from the Perm space and is reverted back once the output window is closed by the user. Suppose 10GB permanent space has been allocated to a database. However, the actual perm space occupied by tables took up 50% of perm space i.e. 5GB. So remaining 5GB space is available system wide for spool. Spool space for the child will not be subtracted from its immediate parents, but the child database spool space can be as much as it immediate parent. Spool space for sysdba and appdba is same as DBC.

TEMPORARY SPACE:-

This space is mostly used in the global temporary tables. Unused Perm space is used as Temp space for storing the table and the data in it. If you are specifying some value for Temp space it should not exceed the value of the parent database/user. If you do not specify a value, the maximum value is inherited from that of the parent.