In Teradata, data is stored on AMPs based on primary index. When calculating space usage on Teradata, we need to calculate all the storage allocation on all the AMPs.
Use DBC.DiskSpaceV[X]
This view can be used to check space usage of any account, database or user.
Example:
SELECT Vproc, DatabaseName,AccountName, MaxPerm, MaxSpool, MaxTemp, CurrentPerm, CurrentSpool FROM DBC.DiskSpaceV
WHERE DatabaseName='TestDb';
Sample output:
Vproc DatabaseName AccountName MaxPerm MaxSpool MaxTemp CurrentPerm CurrentSpool
1 0 TestDb DBC 500,000 19,800,327,618 19,800,327,618 129,536 0
2 1 TestDb DBC 500,000 19,800,327,618 19,800,327,618 129,536 0
*The unit is bytes.
To calculate the total space, use the following query:
SELECT DatabaseName,SUM(CurrentPerm) AS CurrentPermTotal, SUM(MaxPerm) AS MaxPermTotal FROM DBC.DiskSpaceV
WHERE DatabaseName='TestDb'
GROUP BY DatabaseName;
Sample output:
DatabaseName CurrentPermTotal MaxPermTotal
1 TestDb 259,072 1,000,000
Calculate space utilization rate
The following query can be used to calculate space utilization rate:
SELECT DatabaseName,SUM(CurrentPerm) AS CurrentPermTotal, SUM(MaxPerm) AS MaxPermTotal,
((SUM(CurrentPerm))/ NULLIFZERO(SUM(MaxPerm)*1.0000) * 100)(FORMAT 'zz9.99%', TITLE 'Percent Used')
FROM DBC.DiskSpaceV
WHERE DatabaseName='TestDb'
GROUP BY DatabaseName;
Sample output:
DatabaseName CurrentPermTotal MaxPermTotal Percent Used
1 TestDb 259,072 1,000,000 25.9100
Use DBC.GlobalDBSpaceV[X]
We can use this view to report database level information that is aggregated from AMPs.
SELECT DatabaseName, MaxPerm, MaxSpool, MaxTemp, AllocatedPerm, MaxPerm-AllocatedPerm (TITLE 'Unallocated') FROM DBC.GlobalDBSpaceV
WHERE DatabaseName='TestDb';
Sample output:
DatabaseName MaxPerm MaxSpool MaxTemp AllocatedPerm Unallocated
1 TestDb 1,000,000 39,600,655,236 39,600,655,236 1,000,000 0