There are three types of spaces available in Teradata namely Permanent Space, Spool Space & Temporary Space

Type of spaces in Teradata

There are three types of spaces available in Teradata.

Permanent Space
  • Permanent space is the maximum amount of space allocated to the user/database to hold data rows.
  • Perm space is used for database object (permanent tables, indexes etc) creation and to hold their data.
  • The amount of permanent space is divided among the number of AMPs.Whenever per AMP limit exceeds the allocated space of AMP, 'No more room in database' error message is generated.
Spool Space
  • Spool space is the unused permanent space which is used by the system to keep the intermediate results of the SQL query.
  • Users without spool space cannot execute any query.
  • Data is active up to the current session only.
  • Spool space is divided among the number of AMPs. Whenever per AMP limit exceeds the allocated space, the user will get a spool space error.
Temporary Space
  • Temporary space is the unused permanent space which is used by Global Temporary tables.
  • Temp space is also divided by the number of AMPs.
  • Data is active up to the current session only.
  • Temp space is reserved prior to spool space for any user defined operation.
  • Temp space is allocated at the database or user level, but not the table level.

Permanent, Spool & Temporary Space of a Database 

SELECT DatabaseNameI, PermSpace, SpoolSpace, TempSpace,CreateTimeStamp
FROM DBC.DBase 
WHERE DatabaseNameI = '[databasename]';
 
DatabaseNameI        PermSpace        SpoolSpace    TempSpace     CreateTimeStamp
---------------  -------------  ----------------  -----------  -------------------
Tutorial_DB      7,830,765,225   249,999,999,912  999,999,864  2018-07-29 18:33:59