Skip to content

How to Resolve ORA-08186: invalid timestamp specified

  • Oracle

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.

Leave a Reply

Your email address will not be published. Required fields are marked *