Teradata Space Consumption Queries

Space Consumption

In order to find the space consumed by tables and database and also to find out the free space available, following queries will be useful. There are different queries to find each of the following.

1. Total Space Available in a Database
2. Used Space in Database
3. Free Space in Database
4. Space Consumed by each Tables
5. Space Consumed by each table by the creator name.

We will make use of some of the system tables which stores the space information.

Total Space, Used Space and Free Space available in Database :

Below query gives the total space consumed by the database and free space available. This helps to identify whether the database is nearing its full capacity so that we can add additional space or clean up some space.

SELECT
        DATABASENAME DATABASE_NAME,
        SUM(MAXPERM)/(1024*1024*1024) TOTAL_SPACE_IN_GB,
        SUM(CURRENTPERM)/(1024*1024*1024) USED_SPACE_IN_GB,
        TOTAL_SPACE_IN_GB-USED_SPACE_IN_GB AS FREE_SPACE_IN_GB
 FROM
        DBC.DISKSPACE
 WHERE
        DATABASENAME ='<DATABASENAME>'
GROUP BY 1;

Size of Each Tables:

Below query is used to find the size of each table or the space consumed by each table. This will be useful to find the bigger tables in Database and can be identified if the table is needed or not so that space can be freed up.

SELECT      
DATABASENAME,
TABLENAME,
SUM(CURRENTPERM/(1024*1024*1024)) AS "TABLE SIZE IN GB"
FROM
DBC.TABLESIZE
WHERE
DATABASENAME = <'DATABASE_NAME'> AND TABLENAME = <'TABLE_NAME'>
GROUP BY 1,2;

Note: Remove the Tablename filter, if you want to find the table sizes of all the tables in the database.

TableSize by Creator/Owner Name:

This will help us to identify the tables created by other users in the database and check with them if the table is needed or not so that it can be dropped for getting free space.

SELECT        
S.DATABASENAME,
S.TABLENAME,
T.CREATORNAME,
SUM(CURRENTPERM/(1024*1024*1024)) AS "TABLE SIZE IN GB"
FROM
DBC.TABLESIZE  S
JOIN
DBC.TABLES  T
ON
S.TABLENAME=T.TABLENAME AND S.DATABASENAME=T.DATABASENAME
WHERE
S.DATABASENAME = <'DATABASE_NAME'>
GROUP BY 1,2,3;