15.00 - Modifying the Disk Space Currently Assigned to Databases, Users, or Profiles - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Modifying the Disk Space Currently Assigned to Databases, Users, or Profiles

Modifying the permanent, temporary, and spool space assignments for databases, user, and profiles is a simple operation using MODIFY DATABASE, MODIFY USER, and MODIFY PROFILE requests as appropriate.

You can use the system views listed here to help determine new values to for permanent, temporary, and spool space assignments. For more information about these views, see Data Dictionary.

  • DBC.AllSpaceV[X]
  • Use this view to report disk space usage, including spool space, for any account, database, table, or user.

    The following request reports how the space currently used by the department table is distributed on each AMP.

     
         SELECT DatabaseName, TableName, AMP, CurrentPerm 
         FROM DBC.AllSpaceV
         WHERE TableName = ’department’ 
         ORDER BY 1,2,3;
     
         DatabaseName   TableName      AMP       CurrentPerm
         ------------   ----------     ----      ------------
         test           department     1-0       1,024
         test           department     1-1         512
         test           department     1-2       1,024
         test           department     1-3         512
         personnel      department     1-0       2,048
         personnel      department     1-1       1,536
         personnel      department     1-2       1,536
         personnel      department     1-3       1,536
         user1          department     1-0       2,048
         user1          department     1-1       1,536
         user1          department     1-2       1,536
         user1          department     1-3       1,536

     

    The following request reports the values for the MaxPerm and CurrentPerm columns for each table contained by user. Because user only contains one table, employee, the request only returns information about employee and All, where the All “table” represents all of the tables contained by the specified database or user. In this case, All represents all of the tables contained by the user named user.

    The MaxPerm value for All is the amount of permanent space defined for user. Because user contains only one table, the number of bytes on each AMP is the same for both All and employee. All of the reported values represent the size of the employee table.

    Note that employee returns 0 bytes in the MaxPerm column because tables do not have MaxPerm space. Only databases and users have MaxPerm space, represented by All.

     
         SELECT Vproc, TableName (FORMAT ‘X(20)’), MaxPerm, CurrentPerm
         FROM DBC.AllSpaceV
         WHERE DatabaseName = user
         ORDER BY TableName, Vproc;
     
    Vproc    TableName        MaxPerm       CurrentPerm
    -----    ---------        ---------     -----------
        0    All              2,621,440          64,000
        1    All              2,621,440          64,000
        2    All              2,621,440         112,640
        3    All              2,621,440         112,640
        …    …                       …               …
        0    employee                 0          41,472
        1    employee                 0          41,472
        2    employee                 0          40,960
        3    employee                 0          40,960
        …    …                       …               …

     

  • DBC.DiskSpaceV[X]
  • Use this view to report disk space usage, including spool space, for any account, database, or user.

    The following request reports the permanent disk space across all AMPs.

     
         SELECT AMP, DatabaseName, CurrentPerm, MaxPerm 
         FROM DBC.DiskSpaceV;
     
         AMP  DatabaseName     CurrentPerm       MaxPerm
         ---  ------------     -----------      ---------
         .        .                     .          .
         .        .                     .          .
         0-0      stst14                0       125,000
         0-0      ud12                  0       125,000
         1-0      atest             1,536       125,000
         1-0      a1                    0       247,500
         1-0      btest             3,584         5,000
         1-0      b2test           49,664       250,000
         .        .                     .          .
         .        .                     .          .
         1-1      atest             1,536       125,000
         1-1      a1                    0       247,500
         1-1      btest             3,584         5,000
         1-1      b2test           50,688       250,000
         .        .                     .          .
         .        .                     .          .
         1-2      atest             1,536       125,000
     

    Similarly, you can submit a request like the following to calculate the percentage of space used by a particular database. Note that the request uses a NULLIFZERO specification to avoid a divide by zero exception.

     
         SELECT DatabaseName, SUM(MaxPerm), SUM(CurrentPerm), 
                ((SUM (CurrentPerm))/ NULLIFZERO (SUM(MaxPerm)) * 100)
                (FORMAT ‘zz9.99%’, TITLE ‘Percent // Used’)
         FROM DBC.DiskSpaceV
         GROUP BY 1
         ORDER BY 4 DESC;
     

    This request reports the following information from DBC.DiskSpaceV.

     
         DataBaseName     Sum(MaxPerm)     Sum(CurrentPerm)   Percent
                                                               Used
         ------------     --------------   -----------------  -------
         Finance           1,824,999,996      1,796,817,408   98.46%
         DBC              12,000,000,006      8,887,606,400   73.98%
         Spool_Reserve     2,067,640,026        321,806,848   15.56%
         CrashDumps          300,000,000         38,161,408   12.72%
         SystemFE              1,000,002             70,656    7.07%
  • DBC.TableSizeV[X]
  • Use this view to report disk space usage excluding spool space, for any account or table.

    The following request reports the total disk space currently used by the employee table with its peak space usage.

     
         SELECT SUM(PeakPerm), SUM(CurrentPerm)
         FROM DBC.TableSizeV
         WHERE TableName = ’employee’;
     
         Sum(PeakPerm)   Sum(CurrentPerm)
         -------------   ----------------
         260,608         260,608

    In this case, the 2 sums match, indicating that the disk space currently used by the employee table is also its peak space usage.

    The following request reports poorly distributed tables by returning the CurrentPerm values for table_2 and table_2_nusi, the only tables contained by user across all AMPs.

     
         SELECT Vproc, TableName (FORMAT ‘X(20)), CurrentPerm, PeakPerm
         FROM DBC.TableSizeV
         WHERE DatabaseName = user
         ORDER BY TableName, Vproc;

     

         Vproc     TableName         CurrentPerm     PeakPerm
         -----     ---------         -----------     --------
             0     table_2                41,472       53,760
             1     table_2                41,472       53,760
             2     table_2                40,960       52,736
             3     table_2                40,960       52,736
             4     table_2                40,960       52,760
             5     table_2                40,960       52,760
             6     table_2                40,960       52,272
             7     table_2                40,960       52,272
             0     table_2_nupi           22,528       22,528
             1     table_2_nupi           22,528       22,528
             2     table_2_nupi           71,680       71,680
             3     table_2_nupi           71,680       71,680
             4     table_2_nupi            9,216        9,216
             5     table_2_nupi            9,216        9,216
             6     table_2_nupi           59,392       59,392
             7     table_2_nupi           59,392       59,392
     

    The report indicates that table_2 is evenly distributed across the AMPs, but that table_2_nusi is not, with its current permanent space clumped into spaces of 9,216 bytes, 22,528 bytes, 59,392 bytes, and 71,680 bytes across 2 AMPs each.