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