access_time 20 days ago languageEnglish
more_vert

Calculate Teradata Database Size

visibility 9 comment 0

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

copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn or Twitter.

Want to contribute on Kontext to help others?

Learn more