Housekeeping on an Ad-Hoc Basis - Teradata Database

Teradata Database Administration

Teradata Database
Release Number
English (United States)
Last Update
Product Category

You should occasionally clean out rows or reset the fields in the following tables as needed:

  • DBC.AccessRights table
  • When a user or database is created, there is a default number of privileges added to DBC.AccessRights. These entries should be reviewed and obsolete or redundant entries deleted as necessary. Otherwise, this table can grow very large and can greatly impact space limits negatively. This table may also potentially become skewed.

    In addition, when an object is dropped, the system may do a full table scan against DBC.AccessRights. If DBC.AccessRights is large, this can adversely affect the performance of the drop operation. The smaller this table can be made, the better.

    To reduce the size of the DBC.AccessRights table:

  • Determine which users have the most entries. The following query checks for the userid/database ID that has the most rows that hash to the same value:
  • .set retlimit 10
    .set ret cancel
    SELECT COUNT(*) (TITLE '# of//Entries', FORMAT 'ZZZZZZ9'),
     a.userid|| a.databaseid (TITLE 'UserID|DatabaseID'),
     dbase.databasenamei (TITLE 'UserName')
    FROM accessrights a , dbase
    WHERE a.userid = dbase.databaseid
    GROUP BY 2,3 ORDER BY 1 DESC,2 ;
  • REVOKE rights at the table level and GRANT rights at the database level to reduce the number of entries.
  • Convert individual privileges to role privileges where possible.
  • DBC.TSETQueryText table
  • If you receive a 3610 error, the query text may be saved to the DBC.TSETQueryText table for use by the Teradata Customer Support Center. After the problem is resolved, you may need to manually delete the rows where the “Handled” column is set to 1 or has a timestamp that indicates the information is no longer needed.

  • Archive and recovery tables
  • When associated removable media is expired and over-written, clean out the following:





    archive or restore configuration


    VolSerial for archive or restore


    archive or restore events

  • Accumulated values in Data Dictionary tables
  • Resetting values can help you do better resource usage analysis, and keep cleaner historical records. The following fields should be reset when necessary:




    PeakPermSpace, PeakSpoolSpace, and PeakTempSpace in the DBC.DataBaseSpace table.

    Contains database and table space accounting information. To reset the values, execute the macro:

    EXEC DBC.ClearPeakDisk;

    Due to the importance of tracking space usage in your system, you should try to run this macro regularly. Or, you can submit an UPDATE statement yourself.

    CPUTime and DiskIO of the DBC.AMPUsage view (which corresponds to the DBC.Acctg table).

    Holds resource usage data by Acct/User. To reset the values, submit the following:

    SET CPUTime = 0, 
    DiskIO = 0

    AccessCount and LastAccessTimeStamp of the following views:

  • DBC.ColumnsV
  • DBC.DatabasesV
  • DBC.IndicesV
  • DBC.TablesV
  • DBC.UsersV
  • Tracks the number of times and the last time a particular object was accessed.

    To reset these fields, use one of these macros:

  • ClearAllDatabaseUseCount
  • ClearDatabaseUseCount
  • ClearTVMUseCount