Use of the AllSpaceV View - 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
Language
English (United States)
Last Update
2023-11-03
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantageā„¢

The DBC.AllSpaceV view provides space usage information at the object level (table, join index, permanent journal, or stored procedures) and the database/user level. However, Teradata recommends using the DBC.DiskSpaceV and DBC.TableSizeV views instead because DBC.AllSpaceV can return misleading results.

For example, the following query returns the current permanent space from the detail rows and summary rows for each user or database. Note that the view sums data from both types of rows:

SELECT DatabaseName
       ,Sum(CurrentPerm)
       FROM DBC.AllSpaceV
       GROUP BY 1 having sum(currentperm) > 0
       ORDER BY 2 desc;

Result:

DatabaseName                       Sum(CurrentPerm)
------------------------------  -------------------
DBC                                      47,183,872
Sys_Calendar                              2,648,064
SysAdmin                                  1,380,352
SYSLIB                                      319,488
SystemFe                                    148,480

However, a similar query using DBC.DiskSpaceV will return the desired result and, on most systems, will run faster:

SELECT DatabaseName
       ,Sum(CurrentPerm)
       FROM DBC.DiskSpaceV
       GROUP BY 1 having sum(currentperm) > 0
       ORDER BY 2 desc;

Result:

DatabaseName                       Sum(CurrentPerm)
------------------------------  -------------------
DBC                                      23,591,936
Sys_Calendar                              1,324,032
SysAdmin                                    690,176
SYSLIB                                      159,744
SystemFe                                     74,240

The view DBC.DiskSpaceV includes only summary rows and the view DBC.TableSizeV includes only detail rows. The view DBC.AllSpaceV, however, includes both summary and detail rows and when you sum currentperm from AllSpaceV, you get the sum from both the summary and detail rows added together.

If you want space usage information at the database/user level, use the DBC.DiskSpaceV view which selects only summary (user/database) rows. If you want space usage information at the object level, use the DBC.TableSizeV view.