16.10 - About Creating a Macro for Space Usage Reporting - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

You can create a macro like the one shown here.

Example of Creating a Macro for Space Usage Reporting

The following macro shows space used.

CREATE MACRO superspace as (
SELECT databasename, SUM(maxperm), SUM(currentperm)
  FROM DBC.DiskSpaceV
  GROUP BY databasename
  ORDER BY databasename
  WITH SUM(maxperm), sum(currentperm);
);

When executed, the macro returns the following results.

execute superspace;
*** Query completed. 28 rows found. 3 columns returned.
*** Total elapsed time was 1 second.
                                                                       
                                                                       
DatabaseName                  Sum(MaxPerm)    Sum(CurrentPerm)
---------------------       --------------  ------------------
macro                                                                  
All                                      0                   0
cliuser                         10,000,000               7,168
CONSOLE                            100,000                   0
Crashdumps                   1,024,000,000                   0
Custo                        5,120,000,000         100,082,688
CUSTOMER_SERVICE                10,000,000               5,632
dash                            20,000,000               9,216
DBC                         24,316,695,812          89,080,832
Default                                  0                   0
Test_User                       20,000,000               4,096
explainsample                    1,000,000              12,288
EXTUSER                                  0                   0
mkt_bsktTest                 1,280,000,000           1,108,992
PDTBASE                         10,000,000              33,792
PUBLIC                                   0                   0
qttest                         500,000,000              10,240
SQLJ                           600,000,000                   0
SysAdmin                        40,000,000           1,693,696
SYSLIB                          10,000,000              12,288
SYSSPATIAL                     110,000,000           2,931,712
SystemFe                        60,000,000             207,872
SYSUDTLIB                      100,000,000                   0
Sys_Calendar                    15,000,000           2,654,208
TDPUSER                                  0                   0
testdb                             800,000             133,120
testuser                        20,000,000               9,216
udtuser                         10,000,000                   0
                       -------------------  ------------------
Sum(MaxPerm)                33,277,595,812         197,997,056

For the latest example queries for obtaining space usage information, see Knowledge Article CA9001181E6 located in the Teradata Database Knowledge Repositories. This article is available through Teradata @ Your Service.