How to check the table size using query in Teradata
Contents
Table size in Teradata:
DBC.ALLSPACE is the system table used to find the space occupied for each table or database in Teradata.
We can measure the space used by the table in bytes across databases also.
CURRENTPERM column in DBC.ALLSPACE:
The total number of bytes(including table headers) currently allocated to existing data tables,index tables and sub tables,stored procedures,triggers and permanent journals residing in a particular database/user. This value is maintained on each AMP.
Query to get the Table size:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT DATABASENAME, TABLENAME, SUM (CURRENTPERM)/1024**2 AS CURRENT_MB, SUM (CURRENTPERM)/1024**3 AS CURRENT_GB FROM DBC.ALLSPACE WHERE DATABASENAME = 'DATABASE_NAME' AND TABLENAME = 'TABLE_NAME' GROUP BY 1,2 ORDER BY 1,2 |
Example:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT DATABASENAME, TABLENAME, SUM (CURRENTPERM)/1024**2 AS CURRENT_MB, SUM (CURRENTPERM)/1024**3 AS CURRENT_GB FROM DBC.ALLSPACE WHERE DATABASENAME = 'Banking_DB' AND TABLENAME = 'Customer' GROUP BY 1,2 ORDER BY 1,2 |
Output:
Query to find the Database size:
|
1 2 3 4 5 6 7 |
SELECT DATABASENAME, SUM (CURRENTPERM)/1024**2 AS CURRENT_MB, SUM (CURRENTPERM)/1024**3 AS CURRENT_GB FROM DBC.ALLSPACE WHERE DATABASENAME = 'Banking_DB' GROUP BY 1 |
Output:
Recommended Articles
