access_time 4 months ago languageEnglish
more_vert

Calculate Teradata Database Size

visibility 76 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.

Want to contribute on Kontext to help others?

Learn more

More from Kontext

visibility 1312
thumb_up 0
access_time 4 years ago
visibility 45
thumb_up 0
access_time 7 months ago
visibility 4603
thumb_up 1
access_time 3 years ago