Use a flashback query to query data of multiple versions

This topic describes how to use the flashback query feature of OceanBase Database to query data of multiple versions and the use constraints of this feature.

OceanBase Database supports record-specific flashback queries, which allow you to obtain data of a specific historical version. You can use the AS OF SCN and AS OF TIMESTAMP clauses to perform flashback queries in Oracle mode and the AS OF SNAPSHOT clause to perform flashback queries in MySQL mode.

Flashback query methods

OceanBase Database allows you to run flashback queries by using the following methods.

• Create a restore point

  After you create a restore point, you can use the flashback query feature to obtain data of the version corresponding to the restore point. For more information about the procedure of creating and using a restore point, see Restore point.

• Set the undo_retention variable.

  After you set the undo_retention variable, you can use the flashback query feature to obtain data of multiple versions in the time range from T to T - undo_retention.

  This topic shows you how to set the undo_retention variable to perform a flashback query.

Considerations

When you use the undo_retention variable to perform a flashback query, note the following points:

• If you set the undo_retention variable to T0, this feature takes effect on data generated after T0.

• If the queried table is moved to the recycle bin, you must restore it from the recycle bin before you can query data in the table.

  For more information about the operation of flashing back a table, see Flash back objects in the recycle bin.

• After you set the current time to T and set the undo_retention variable, if a DDL operation is performed in the time range specified in T - undo_retention, the system uses the latest data dictionary to explain the data in the table. For data generated before the DDL operation is performed, the deleted column data is changed to the deleted state, and the added column data is set to the default value.

Set variables

Before you set the undo_retention variable to perform a flashback query, modify the value of the undo_retention variable. The default value of the undo_retention variable is 0, in seconds.

1. Log on to the corresponding tenant as the tenant administrator.

2. Run the following command to modify the value of the undo_retention variable.

   For example:

   obclient> SET GLOBAL undo_retention=900;
   

   For more information about the undo_retention variable, see undo_retention.

Run a flashback query in MySQL mode

Assume that the undo_retention variable is set to 900 and a user table table1 exists.

Specify a historical point in time by using AS OF SNAPSHOT and query the status and data of a single table at this point in time.

obclient> SELECT * FROM table1 AS OF SNAPSHOT 1582807800000000;

1582807800000000 is the timestamp, in μs. You must convert the time to the required format.

Run a flashback query in Oracle mode

Suppose that the value of the undo_retention variable is set to 900 and three tables table1, table2, and expr1 exist.

The following examples show the sample code for flashback queries in Oracle mode.

• Specify a historical point in time by using TIMESTAMP and query the status and data of a single table at this point in time:

  obclient> SELECT * FROM table1 AS OF TIMESTAMP TO_TIMESTAMP('2020-08-13 16:20:00','yyyy-mm-dd hh24:mi:ss');
  

• Specify a historical point in time by using TIMESTAMP and query the status and data of the multiple tables at this point in time:

  obclient> SELECT * FROM table1 AS OF TIMESTAMP expr1,table2 AS OF TIMESTAMP TO_TIMESTAMP('2020-08-13 16:20:00','yyyy-mm-dd hh24:mi:ss');
  

• Specify a historical point in time by using SCN and query the status and data of a single table at this point in time:

  obclient> SELECT * FROM table1 AS OF SCN 1582807800000000;
  

  1582807800000000 is the timestamp, in μs. You must convert the time to the required format.