Check System/Database/Table/Query Space | Teradata Vantage - SQL Requests for Checking Space - Teradata Vantage

Configuring Teradata Vantage™ After Installation
Product
Teradata Vantage
Release Number
2.2
Published
January 2021
Language
English (United States)
Last Update
2021-05-08
dita:mapPath
gta1588198788318.ditamap
dita:ditavalPath
dby1605127568110.ditaval
dita:id
B700-4014
Product Category
Analytical Ecosystem
Software
Teradata Vantage

From a client application, such as BTEQ or Teradata Studio, run the following optional queries to check space.

In BTEQ, to make sure you can see all result columns, run these commands before you run the SQL requests:
.sidetitles on
.foldline on ALL
To check... Run this request...
Total available disk space in the system (MaxPerm) 
SELECT SUM(MaxPerm) FROM DBC.DiskSpaceV;
Maximum database space (MaxPerm) 
SELECT SUM(t3.Maxperm) FROM
(SELECT
t1.DatabaseName
,t1.Permspace AS Maxperm
,ZEROIFNULL(SUM(t2.CurrentPerm)) AS Currentperm
FROM dbc.databases t1
LEFT OUTER JOIN dbc.tablesize t2
ON (t1.databasename=t2.databasename)
WHERE (Maxperm > 0)
GROUP BY 1,2) t3
;
Database space: maximum available and currently in use 
Select
t1.DatabaseName
,t1.Permspace AS Maxperm
,ZEROIFNULL(sum(t2.CurrentPerm)) AS Currentperm
FROM dbc.databases t1
LEFT OUTER JOIN dbc.tablesize t2
ON (t1.databasename=t2.databasename)
WHERE (Maxperm > 0)
GROUP BY 1,2
ORDER BY 1;
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 'z z9.99')
FROM DBC.DiskSpaceV;
Spool space on active queries 
Select
Vproc
,DatabaseName
,MaxSpool
,CurrentSpool
,PeakSpool
from dbc.diskspace
where currentspool > 0
order by 2,1
;
Users who are running out of permanent 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;
You can change the value 0.9 to whatever threshold ratio is appropriate for your site.
Percentage 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 using excessive 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 a greater tolerance for higher spool usage and spool limits.
Unallocated space remaining in the database 
SELECT databasename, allocatedperm, maxperm, permskew, maxperm-allocatedperm (TITLE 'UnallocatedSpace') FROM DBC.GlobalDBSpaceV;
Maximum allocated peak spool for a given user 
SELECT databasename, PeakAllocatedSpool from DBC.GlobalDBSpaceV;
Disk space for a given table 
SELECT SUM(currentperm)
FROM DBC.tablesizeV
WHERE databasename='xxx'
AND tablename = 'xxxx';