Flashback queries

This topic describes the flashback query feature of OceanBase and the use constraints of this feature.

Oracle supports record-level flashback queries, to allow you to obtain data of a specific historical version. OceanBase Database provides this feature starting from V2.2.30 in Oracle mode and starting from V2.2.71 in MySQL mode. The two modes vary in syntax.

In Oracle mode, OceanBase Database supports flashback queries by system change number (SCN) or TIMESTAMP. In MySQL mode, OceanBase Database supports flashback queries by SNAPSHOT. Both the SCN and the SNAPSHOT indicate the transaction version. The different terms are used due to different concepts in Oracle and MySQL.

Example in MySQL mode

The following command specifies a historical point in time by using a SNAPSHOT and queries the status and data of a single table at this point in time:

obclient> SELECT * FROM table1 AS OF SNAPSHOT 1582807800000000;

Example in Oracle mode

The following are sample commands for flashback queries in Oracle mode.

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

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

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

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

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

  obclient> SELECT * FROM table1 AS OF SCN 1582807800000000;
  

Use constraints

Use constraints on flashback queries are as follows:

• The data that can be queried by using flashback queries is subject to the value of undo_retention, which is counted in seconds and set to 0 by default. Assume that t1 is the start time for data retention and t2 = t1 + 900s, you can query data generated within [t1, t2] at t2. The setting takes effect only on data generated after undo_retention is configured.

  You can set the undo_retention parameter by using the following statement:

  obclient> SET GLOBAL undo_retention=900;
  

• You can obtain the SCN of an OceanBase Database from the v$ob_timestamp_service view. When you query data under a system tenant, information about all tenants is displayed. When you query data under a common tenant, information about the tenant is displayed.

• You can query the latest major compaction version at a specific point in time. If you initiate a major compaction for a cluster at t1, the earliest data that you can query is data generated at t1.

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

• Flashback queries are subject to minor compactions. If a minor compaction has been performed and the undo_retention parameter is not specified, flashback queries cannot be performed. After undo_retention is specified, you can query data generated within the time range specified by undo_retention, starting from t1 at which the minor compaction is performed.