"table definition changed" despite restore point creation after table create/alter

Question

FLASHBACK TABLE to a restore point fails when that restore point was created immediately after a table change. The below code only works if there is a sleep between certain steps.

SQL> DROP TABLE TEST_TABLE;

Table dropped.

SQL> CREATE TABLE TEST_TABLE AS SELECT 1 A FROM DUAL;

Table created.

SQL> ALTER TABLE TEST_TABLE ENABLE ROW MOVEMENT;

Table altered.

SQL> --Sleep required here to prevent error on flashback.
SQL> DROP RESTORE POINT TEST_RESTORE_POINT;

Restore point dropped.

SQL> CREATE RESTORE POINT TEST_RESTORE_POINT;

Restore point created.

SQL> FLASHBACK TABLE TEST_TABLE TO RESTORE POINT TEST_RESTORE_POINT;
FLASHBACK TABLE TEST_TABLE TO RESTORE POINT TEST_RESTORE_POINT
                *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

Why is a delay required and is there a way to eliminate it?

Answer 1

This oddity might be caused by SMON process which is responsible to keep track between SCNs and timestamps which flashback query relies upon. There is a mapping table SYS.SMON_SCN_TIME where every 5 minutes a new record is inserted SMON.

Internally during the FLASHBACK TABLE executes a command INSERT /*+ APPEND */ into SYS_TEMP_FBT SELECT /*+ FBTSCAN FULL(S) PARALLEL(S, DEFAULT) */ :1, :2, :3, rowid, SYS_FBT_INSDEL FROM "<schema>."TEST_TABLE" as of SCN :4 S (notice a table SYS_TEMP_FBT is created in the same schema) which uses this mapping.

Up to Oracle 10.2 you needed to wait up to whole 5 minutes to succeed with FLASHBACK query on a new/altered object. In 11.1 the TIM_SCN_MAP column was introduced to make the mapping more fine grained. Maximum of 100 mappings is stored in one value which makes roughly 3 seconds precision in timestamp to SCN mapping.

I tried many things but I don't think you can do anything about it but wait around 3 seconds to avoid the error because this is handled asynchronously by background process without any user control.