15.10 - AllSpaceV[X] - Teradata Database

Teradata Database Data Dictionary

Product
Teradata Database
Release Number
15.10
Content Type
Administration
User Guide
Publication ID
B035-1092-151K
Language
English (United States)

Accounting

DBC

 

View Column

Data Type

Format

Source Table.Column

Vproc

SMALLINT 

NOT NULL

-(5)9

DataBaseSpace.Vproc

DatabaseName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

Dbase.DatabaseName

ConnectRulesTbl.ProxyUser

AccountName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

Dbase.AccountName

TableName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

TVM.TVMName

MaxPerm

FLOAT

NOT NULL

----,---,---,---,--9 (explicit)

DataBaseSpace.MaxPermSpace

MaxSpool

FLOAT 

NOT NULL

----,---,---,---,--9 (explicit)

DataBaseSpace.MaxSpoolSpace

MaxTemp

FLOAT 

NOT NULL

----,---,---,---,--9 (explicit)

DataBaseSpace.MaxTempSpace

CurrentPerm

FLOAT 

NOT NULL

----,---,---,---,--9 (explicit)

DataBaseSpace.CurrentPermSpace

CurrentSpool

FLOAT 

NOT NULL

----,---,---,---,--9 (explicit)

DataBaseSpace.CurrentSpoolSpace

CurrentPersistentSpool

FLOAT 

NOT NULL

----,---,---,---,--9 

DataBaseSpace.CurrentPersistent
SpoolSpace

CurrentTemp

FLOAT 

NOT NULL

----,---,---,---,--9 (explicit)

DataBaseSpace.CurrentTempSpace

PeakPerm

FLOAT

NOT NULL

----,---,---,---,--9 (explicit)

DataBaseSpace.PeakPermSpace

PeakSpool

FLOAT 

NOT NULL

----,---,---,---,--9 (explicit)

DataBaseSpace.PeakSpoolSpace

PeakPersistentSpool

FLOAT 

NOT NULL

----,---,---,---,--9 

DataBaseSpace.PeakPersistentSpool
Space

PeakTemp

FLOAT 

NOT NULL

----,---,---,---,--9 (explicit)

DataBaseSpace.PeakTempSpace

MaxProfileSpool

FLOAT

----,---,---,---,--9 (explicit)

DataBaseSpace.MaxProfileSpoolSpace

MaxProfileTemp

FLOAT

----,---,---,---,--9 (explicit)

DataBaseSpace.MaxProfileTempSpace

TrustUserName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

Dbase.DatabaseName

AppProxyUser

CHAR(1)

X(1)

None

The X view references these additional tables:

  • DBC.AccessRights
  • DBC.Owners
  • DBC.Roles 
  • DBC.RoleGrants
  • When a database, user, or table is created, allocated disk space is divided evenly among all AMPs. The AllSpace view returns one row of usage information for each AMP in the Teradata Database configuration (or for all AMPs if the SUM aggregate is used).

    When a database is created, a space row is added to each AMP, with the processor field in each row initialized to 0. The first time the space row is updated (such as when a table is created in the database, or when the system is restarted), the processor field in each row is updated to indicate the actual processor number.

    When a query applies a SUM aggregate to either the MaxPerm or CurrentPerm column without a WHERE clause, or with a WHERE clause that references only one TableName or DatabaseName, the returned values are double the desired result.

    For example, the following query, which returns the correct amount of space allocated to Peterson, also returns twice the amount of space currently being used by Peterson (see “DiskSpaceV[X]” on page 125 and “TableSizeV[X]” on page 328).

    SELECT SUM(MaxPerm), SUM(CurrentPerm)
       FROM DBC.AllSpaceV
       WHERE DatabaseName = ’Peterson’;

    AccountName

    The AccountName column is the unexpanded default account name for the user or database. A database can only have one account. A user may have a default account plus other accounts. If there is a profile for the user, and it specifies a default account, that account superseded this account name.

    CurrentPerm

    The CurrentPerm column value includes all AMPs unless a specific AMP is requested.

    CurrentPersistentSpool

    This column returns the persistent spool space (in bytes) currently used by the database on a specified AMP or on all AMPs if the SUM aggregate is specified.

    PeakPerm, PeakSpool, PeakPersistentSpool, and PeakTemp

    You can use the DBC.ClearPeakDisk macro to reset the PeakPerm, PeakSpool, PeakPersistentSpool, and PeakTemp columns.

    PeakPersistentSpool

    This column returns the integer that represents the maximum persistent spool space (in bytes) that was used at any one time by the database on a specified AMP or on all AMPs if the SUM aggregate is specified.

    TrustUserName and AppProxyUser

    TrustUserName returns the trusted user connected as the application proxy user. AppProxyUser set to ‘Y’ indicates the space is for an application proxy user.

    The following SELECT statement displays how the space currently used by the data table named Department is distributed on each AMP:

    ==> SELECT DatabaseName,TableName,AMP,CurrentPerm FROM DBC.AllSpaceV
    WHERE TableName=’Department’ ORDER BY 1,2,3 ;

    Result:

    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