17.10 - LIST ROLLBACK TABLES - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Configuration
Publication ID
B035-1102-171K
Language
English (United States)

The LIST ROLLBACK TABLES command displays the names and details of the tables undergoing rollback in the Teradata system.

Syntax

LIST ROLLBACK TABLES ;

Usage Notes

The tables undergoing rollback must satisfy the following criteria:

  • The table in a transaction must have more than 10000 rows yet to rollback on at least one AMP.
  • The transaction must be in abort status.

The report displays only the headings if no rollback is in progress in the Teradata system, or if no table satisfies these criteria.

The list is in descending order of the Host and Session, and then in alphabetical order by the database name and table name.

The list excludes Permanent Journal (PJ) tables and all DBC tables except DBQL and ResUsage tables.

An asterisk (*) appears after the table name if the table has any Referential Integrity constraints. You cannot cancel rollback on such tables.

Table names having non-printable characters are displayed in hexadecimal.

The list is in two parts, as shown in the following table.
Global Temporary tables and Volatile tables are not listed.
This part... Displays a list of tables in the sessions that are undergoing rollback as part of...
online user rollback tables user-requested abort.
system recovery rollback tables Teradata system recovery.

The following table explains what information the report columns provide:

Column... Specifies the...
Host ID of the host machine.
Session session number in which rollback is in progress.
User ID ID of the user who has initiated the session.
Workload Definition Workload at which all transactions in the session perform.
AMP W/Count AMP on which the maximum number of Transient Journal rows are left to be processed.
TJ Rows Left

TJ Row Count

maximum number of rows left to be processed in the Transient Journal for the specific session on any AMP.
TJ Rows Done number of rows processed in the Transient Journal for the specific session.
Time Est. estimated time (in hours and minutes) left for the rollback to complete for that session.

Example: Displaying a list of the tables undergoing rollback processing

The following is a sample output of the LIST ROLLBACK TABLES command.

TABLES BEING ROLLED BACK AT 14:25:34  02/11/15
        
ONLINE USER ROLLBACK TABLE LIST
        
Host  Session   User ID     Workload Definition   AMP W/Count 
----  --------  ----------  --------------------  -----------
110   23857     0000:0481            NoWD-PG1054            1
        
        
TJ Rows Left   TJ Rows Done   Time Est. 
-------------  -------------  ------------
   30,478        1,098         01:37
 
 
Table ID   Name
---------  ----------------------------------------------
0000:3883  "rcv_user1"."cr_in001"
0000:388B  "rcv_user1"."cr_in002"
0000:3884  "rcv_user1"."cr_ri001"*
0000:3889  "rcv_user1"."cr_ri002"*
 
 
SYSTEM RECOVERY ROLLBACK TABLE LIST
 
Host  Session   TJ Row Count
----  --------  ------------
0         0        30,032
        
Table ID   Name
---------  ----------------------------------------------
0005:045A  "EmployeeDB"."Parent_Table"*
0005:045B  "EmployeeDB"."Temp_Table"
 
* - Referential Integrity table, cannot be used in CANCEL ROLLBACK ON TABLE command.