Query the DiskSpaceV View | Teradata Vantage - Querying the DiskSpaceV View to Find System Space Issues - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2024-10-04
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantage™

The DBC.DiskSpaceV view returns AMP information about disk space usage at the database or user level. It also can report spool space usage. DiskSpaceV figures are calculated only for the space owned by the user submitting the query. To find information about the full system, log on as the topmost user in the hierarchy (usually your site administrative user).

You can determine the amount of space allocated to a profile by querying the MaxProfileSpool and MaxProfileTemp columns of DBC.DiskSpaceV. By querying MaxSpool and MaxTemp of the restricted view (DBC.DiskSpaceVX), you get the space limits for your individual user space. The following queries show the kinds of information you can obtain from DBC.DiskSpaceV.

To find the… Submit the following query…
total disk space in the entire system
SELECT SUM(MaxPerm) FROM DBC.DiskSpaceV;
disk space currently in use
SELECT SUM(CurrentPerm),
SUM(MaxPerm),
((Cast(Sum(Currentperm) as float) /  NullifZero(Cast(Sum(maxperm) as float))
*100)) 
(Title '%MaxPerm', Format 'zz9.99')
FROM DBC.DiskSpaceV;
disk space for a given database
SELECT sum(maxperm)
FROM DBC.DiskSpaceV WHERE databasename='xxxx';
percent of disk space available for spool
SELECT (((cast(SUM(MaxPerm) as float) - cast(SUM(CurrentPerm) as float)) /
NULLIFZERO(cast(SUM(MaxPerm) as float))) * 100)
(TITLE'% Avail for Spool', format'zz9.99')
FROM DBC.DiskSpaceV;
percent of space used by each database in the system
SELECT Databasename (format 'X(12)')
,SUM(maxperm)
,SUM(currentperm)
,((cast(SUM(currentperm) as float))/
NULLIFZERO (cast(SUM(maxperm) as float)) * 100)
(FORMAT 'zz9.99%', TITLE 'Percent // Used')
FROM DBC.DiskSpaceV
GROUP BY 1
ORDER BY 4 DESC
WITH SUM (currentperm), SUM(maxperm);
users who are running out of PERM space
SELECT Databasename (format 'X(12)')
,SUM(maxperm)
,SUM(currentperm)
,((cast(SUM(currentperm) as float))/
NULLIFZERO (cast(SUM(maxperm) as float)) * 100)
(format 'zz9.99%', TITLE 'Percent // Used')
FROM DBC.DiskSpaceV
GROUP BY 1 HAVING (cast(SUM(currentPerm) as float) / NULLIFZERO(cast(SUM(maxperm) as float)))
> 0.9
ORDER BY 4 DESC;
users who are using a lot of spool
SELECT databasename
,SUM(peakspool)
FROM DBC.DiskSpaceV
GROUP BY 1 HAVING SUM(peakspool) > 5000000000
ORDER BY 2 DESC;
You can change the value 500000000 to whatever value is appropriate for your site. Some sites with more space may have higher tolerance for higher spool usage and spool limits.