Flashback Query is a feature provided by OceanBase Database to enable you to view a historical version of the data. By default, SQL queries return the submitted data that is accessible at the current transaction isolation level. To query a historical version of the data, you can specify a historical point in time after the table.
Example: Flashing back historical records
obclient> create table t1 (id number not null primary key, name varchar2(50) not null, gmt_create timestamp default CURRENT_TIMESTAMP );
Query OK, 0 rows affected (0.06 sec)
obclient> create sequence seq_t1 start with 1;
Query OK, 0 rows affected (0.02 sec)
obclient> insert into t1 (id, name) values(seq_t1.nextval, 'A');commit;
Query OK, 1 row affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
obclient> insert into t1 (id, name) values(seq_t1.nextval, 'B');commit;
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
obclient> insert into t1 (id, name) values(seq_t1.nextval, 'C');commit;
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
obclient> insert into t1 (id, name) values(seq_t1.nextval, 'D');commit;
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
obclient> select * from t1;
+----+------+---------------------+
| ID | NAME | GMT_CREATE |
+----+------+---------------------+
| 1 | A | 2020-04-02 17:41:51 |
| 2 | B | 2020-04-02 17:41:57 |
| 3 | C | 2020-04-02 17:42:04 |
| 4 | D | 2020-04-02 17:42:10 |
+----+------+---------------------+
4 rows in set (0.00 sec)
obclient> select * from t1 as of timestamp to_timestamp('2020-04-02 17:41:57','YYYY-MM-DD HH24:MI:SS');
+----+------+---------------------+
| ID | NAME | GMT_CREATE |
+----+------+---------------------+
| 1 | A | 2020-04-02 17:41:51 |
| 2 | B | 2020-04-02 17:41:57 |
+----+------+---------------------+
2 rows in set (0.00 sec)
Flashback queries have a limit on how far back in time you can flash back, which is determined by the UNDO_RETENTION parameter. If the UNDO_RETENTION parameter is not set or is set to 0, you can query the data generated after the last minor freeze following the last major freeze in the cluster. To modify the UNDO_RETENTION parameter, you need the administrator permissions of the tenant. You can use the SET GLOBAL command to modify the parameter. Your changes only apply to data generated in sessions after the modification.
obclient> show variables like 'undo_retention';
+----------------+-------+
| VARIABLE_NAME | VALUE |
+----------------+-------+
| undo_retention | 0 |
+----------------+-------+
1 row in set (0.00 sec)
If you query the data at a minor freeze that has occurred in a cluster, an error is returned.
obclient> select * from t1 as of timestamp to_timestamp('2020-04-02 17:41:57','YYYY-MM-DD HH24:MI:SS');
ORA-08186: invalid timestamp