16.10 - Resetting Use Counts - Teradata Database

Teradata Database Administration

Teradata Database
Release Number
Release Date
April 2018
Content Type
Publication ID
English (United States)

When you enable query logging for a database, system and user use counts and timestamps are reset. However, when you disable query logging, only system counts and timestamps are reset. User use counts and timestamps are not reset. To reset them, use these macros:

This macro... Resets...
ClearAllDatabaseUseCount AccessCount and LastTimeStamp for all objects in the system.
ClearDatabaseUseCount AccessCount and LastTimeStamp of objects in a specified database.
ClearTVMUseCount the AccessCount and LastAccessTimeStamp for all of the objects in a specified table, view, macro, trigger, stored procedure, join index, user-defined type, user-defined method, or user-defined function.
ClearUserUseCount all access counts (excluding statistics) for a particular user.
ClearUserStatCount all statistics counts for a particular user.
ClearUserUDICount all update, delete, and insert counts for a particular user.
You must have the EXECUTE privilege on the macros to use them.

To create these macros, check if the DIP utility script DIPVIEWSV has run. If not, run DIPVIEWSV.

All SQL statements used by the ClearDatabaseUseCount macros place write locks on the database objects involved.

Examples of Resetting Use Counts

TO reset the AccessCount and LastAccessTimeStamp fields for… USE the following syntax…
the table tab1 Exec DBC.ClearTVMUseCount('db1', 'tab1');

Specify the databasename and the tablename in apostrophes, separated by a comma.

the database db2 Exec DBC.ClearDatabaseUseCount('db2');

Specify databasename in apostrophes.

all the objects in the system Exec DBC.ClearAllDatabaseUseCount;