Teradata database space query

As a Teradata Admin or Developer you may want to know the database space utilization in Teradata system. This will provide a clear picture about how the spaces are allocated over the different databases.

Following Teradata database space query will help you out to get an idea about space distribution.

Teradata database space query

SELECT
DatabaseName
,SUM(CurrentPerm)/1024/1024/1024 AS USEDSPACE_IN_GB
,SUM(MaxPerm)/1024/1024/1024 AS MAXSPACE_IN_GB
,SUM(CurrentPerm)/ NULLIFZERO (SUM(MaxPerm)) *100 (FORMAT 'zz9.99%') AS Percentage_Used 
,MAXSPACE_IN_GB- USEDSPACE_IN_GB AS REMAININGSPACE_IN_GB
FROM DBC.DiskSpace
--WHERE DatabaseName = 'dbc'
order by 3 desc
GROUP BY DatabaseName;

Explanation

Here,

  • USEDSPACE_IN_GB indicates current utilized space for each database.
  • MAXSPACE_IN_GB indicates allocated permanent space for each database.
  • Percentage_Used indicates current utilized space in percentage.
  • REMAININGSPACE_IN_GB indicates remaining space for each database.

If you want to see only for a specific database space utilization, you can do so by providing the databasename in the WHERE clause.

If the above query execute, it produces below result.

DatabaseName USEDSPACE
_IN_GB
MAXSPACE
_IN_GB
Percentage_Used REMAININGSPACE
_IN_GB
DBC                            0.15 28.47 0.52 28.32
TERADATAPOINT                  0 5 0 5
financial                      0.02 2.11 0.82 2.09
SystemFe                       0 0.75 0.02 0.74
tdwm                           0 0.09 0.7 0.09
dbcmngr                        0 0.09 0 0.09
twm_md                         0.07 0.07 92.49 0.01
Crashdumps                     0 0.07 0 0.07
tpch                           0.05 0.05 89.18 0.01
SYSLIB                         0 0.04 11.14 0.03
SYSBAR                         0 0.04 0.01 0.04
SYSUDTLIB                      0 0.03 1.1 0.03
retail                         0.02 0.02 95.19 0
SysAdmin                       0 0.02 12.01 0.02
twm_source                     0.01 0.01 79.05 0
Samples                        0 0.01 0.02 0.01
SYSSPATIAL                     0 0.01 61.37 0

Total space in Teradata system

You can find out the total space of Teradata system using the following query.

SELECT
--DatabaseName
 SUM(CurrentPerm)/1024/1024/1024 AS USEDSPACE_IN_GB
,SUM(MaxPerm)/1024/1024/1024 AS MAXSPACE_IN_GB
,SUM(CurrentPerm)/ NULLIFZERO (SUM(MaxPerm)) *100 (FORMAT 'zz9.99%') AS Percentage_Used 
,MAXSPACE_IN_GB- USEDSPACE_IN_GB AS REMAININGSPACE_IN_GB
FROM DBC.DiskSpace;

If the above query execute, it produces below result.

USEDSPACE_IN_GB MAXSPACE_IN_GB Percentage_Used REMAININGSPACE_IN_GB
0.33 36.89 0.88 36.57