ORA-08186
Tried to perform a flashback query, but it failed with ORA-08186.
SQL> select count(*) cnt from t1 as of timestamp to_timestamp('3023-02-20 00:53:08', 'yyyy-mm-dd hh24:mi:ss');
select count(*) cnt from t1 as of timestamp to_timestamp('3023-02-20 00:53:08', 'yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-08186: invalid timestamp specified
ORA-08186 means that the timestamp you specified in the statement is out of scope, please choose a reasonable one for your statement.
Solution
In this case, we did choose an unreasonable year 3023 in the future. So what is the reasonable timestamp that we should choose? You may perform the following query to know it.
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select 'Between "' || last_ddl_time || '" and "' || sysdate || '"' "Reasonable Timestamp" from all_objects where owner = 'HR' and object_type = 'TABLE' and object_name = 'T1';
Reasonable Timestamp
-------------------------------------------------------
Between "2023-02-20 00:52:36" and "2023-02-20 02:25:47"
That is to say, the reasonable point is any time between LAST_DDL_TIME and now, it depends on what point in time you want to use.
SQL> select count(*) cnt from t1 as of timestamp to_timestamp('2023-02-20 00:53:08', 'yyyy-mm-dd hh24:mi:ss');
CNT
----------
107
We solved it.