As a Teradata Admin or Developer you may want to know the database space utilization in Teradata system. This will provide a clear picture about how the spaces are allocated over the different databases.
Following Teradata database space query will help you out to get an idea about space distribution.
Teradata database space query
SELECT DatabaseName ,SUM(CurrentPerm)/1024/1024/1024 AS USEDSPACE_IN_GB ,SUM(MaxPerm)/1024/1024/1024 AS MAXSPACE_IN_GB ,SUM(CurrentPerm)/ NULLIFZERO (SUM(MaxPerm)) *100 (FORMAT 'zz9.99%') AS Percentage_Used ,MAXSPACE_IN_GB- USEDSPACE_IN_GB AS REMAININGSPACE_IN_GB FROM DBC.DiskSpace --WHERE DatabaseName = 'dbc' order by 3 desc GROUP BY DatabaseName;
Explanation
Here,
- USEDSPACE_IN_GB indicates current utilized space for each database.
- MAXSPACE_IN_GB indicates allocated permanent space for each database.
- Percentage_Used indicates current utilized space in percentage.
- REMAININGSPACE_IN_GB indicates remaining space for each database.
If you want to see only for a specific database space utilization, you can do so by providing the databasename in the WHERE clause.
If the above query execute, it produces below result.
| DatabaseName | USEDSPACE _IN_GB |
MAXSPACE _IN_GB |
Percentage_Used | REMAININGSPACE _IN_GB |
| DBC | 0.15 | 28.47 | 0.52 | 28.32 |
| TERADATAPOINT | 0 | 5 | 0 | 5 |
| financial | 0.02 | 2.11 | 0.82 | 2.09 |
| SystemFe | 0 | 0.75 | 0.02 | 0.74 |
| tdwm | 0 | 0.09 | 0.7 | 0.09 |
| dbcmngr | 0 | 0.09 | 0 | 0.09 |
| twm_md | 0.07 | 0.07 | 92.49 | 0.01 |
| Crashdumps | 0 | 0.07 | 0 | 0.07 |
| tpch | 0.05 | 0.05 | 89.18 | 0.01 |
| SYSLIB | 0 | 0.04 | 11.14 | 0.03 |
| SYSBAR | 0 | 0.04 | 0.01 | 0.04 |
| SYSUDTLIB | 0 | 0.03 | 1.1 | 0.03 |
| retail | 0.02 | 0.02 | 95.19 | 0 |
| SysAdmin | 0 | 0.02 | 12.01 | 0.02 |
| twm_source | 0.01 | 0.01 | 79.05 | 0 |
| Samples | 0 | 0.01 | 0.02 | 0.01 |
| SYSSPATIAL | 0 | 0.01 | 61.37 | 0 |
Total space in Teradata system
You can find out the total space of Teradata system using the following query.
SELECT --DatabaseName SUM(CurrentPerm)/1024/1024/1024 AS USEDSPACE_IN_GB ,SUM(MaxPerm)/1024/1024/1024 AS MAXSPACE_IN_GB ,SUM(CurrentPerm)/ NULLIFZERO (SUM(MaxPerm)) *100 (FORMAT 'zz9.99%') AS Percentage_Used ,MAXSPACE_IN_GB- USEDSPACE_IN_GB AS REMAININGSPACE_IN_GB FROM DBC.DiskSpace;
If the above query execute, it produces below result.
| USEDSPACE_IN_GB | MAXSPACE_IN_GB | Percentage_Used | REMAININGSPACE_IN_GB |
| 0.33 | 36.89 | 0.88 | 36.57 |