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. 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;
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 returns the desired result and, on most systems, runs faster:
SELECT DatabaseName ,Sum(CurrentPerm) FROM DBC.DiskSpaceV GROUP BY 1 having sum(currentperm) > 0 ORDER BY 2 desc;
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.