Query to find Database Space Utilization in Teradata

Space allocation is the most primary and important part in any Database.

Teradata gives us the unique set of System Tables, with the help of which, we can do some useful space allocation calculation. Sometimes when there is no more space left in the System then we need to check in the existing databases and retrieve the unused space from them to fulfill our space requirement.

Below mentioned query help us to identify all the Database and the amount of space used by it –

SELECT

DatabaseName (TITLE ‘DatabaseName’)
,SUM(MaxPerm)/(1024*1024*1024) (DECIMAL(15,6)) (TITLE ‘Max Perm (GB)’)
,SUM(CurrentPerm)/(1024*1024*1024) (DECIMAL(15,6)) (TITLE ‘Current Perm (GB)’)
,((SUM(CurrentPerm))/ NULLIFZERO (SUM(MaxPerm)) * 100) (DECIMAL(15,6)) (TITLE ‘Percent Used’)
FROM DBC.DiskSpace
WHERE MAXPERM >0
GROUP BY 1
ORDER BY 4 DESC
;

This Query will return 4 columns DatabaseName, Max Perm (GB), Current Perm (GB) and Percent Used. From them you can analyze  the amount space occupied by each Database in the given teradata system.