getting ORA-08186: invalid timestamp specified each time I tried to run a FVQ.
ORA-08186: invalid timestamp specified
Well ... take a look at the following example
First I wanted to make sure, that the format I use is the correct one.
1* select to_char(systimestamp,'DD-MON-RR HH.MI.SSXFF AM') from dual
SQL> /
TO_CHAR(SYSTIMESTAMP,'DD-MON-RR
-------------------------------
14-DEC-06 10.27.26.622829 AM
Now, when I tried to run FVQ, I got "ORA-30052: invalid lower limit snapshot expression". That was an expected result, since my lower limit did not belong in (SYSDATE-UNDO_RETENTION, SYSDATE] range. (UNDO_RETENTION parameter was set to 900).
But you can agree with me that Oracle successfully processed timestamp values that I used in this query.
SQL> ed
Wrote file afiedt.buf
1 select comm
2 from scott.emp
3 versions between timestamp
4 to_timestamp('14-DEC-06 09.45.00.000000 AM','DD-MON-RR HH.MI.SSXFF AM') and
5 to_timestamp('14-DEC-06 10.00.00.000000 AM','DD-MON-RR HH.MI.SSXFF AM')
6 where
7* empno = 7369
SQL> /
from scott.emp
*
ERROR at line 2:
ORA-30052: invalid lower limit snapshot expression
So I modified the lower limit to fit in the right range, and I got ORA-08186: invalid timestamp specified. !?!?!?
SQL> ed
Wrote file afiedt.buf
1 select comm
2 from scott.emp
3 versions between timestamp
4 to_timestamp('14-DEC-06 10.20.00.000000 AM','DD-MON-RR HH.MI.SSXFF AM') and
5 to_timestamp('14-DEC-06 11.00.00.000000 AM','DD-MON-RR HH.MI.SSXFF AM')
6 where
7* empno = 7369
SQL> /
from scott.emp
*
ERROR at line 2:
ORA-08186: invalid timestamp specified
After some time that I spent trying to resolve this issue (I couldn't dare to open SR about it:-)) I remembered I have had similar problems while trying to test some flashback features (flashback table to before drop) on Sql*Plus 9.2.x while ago….and I was using Sql*Plus 9.2 again.
I tried the same example on Sql*Plus 10.1.0.2
… and everything worked well.
