Avatar of Chandan Pratihast
Chandan Pratihast
Flag for Japan asked on

Can Flashback table work after restore also?

Hi Experts, one more question on Flashback. I have one small table with 100 rows only. It has updates on every batch run. And suppose a batch aborts we want to flashback this. So, enabled the row movement on this table.  And it helps to restore the data of this table. And it is very effective. But the question is can this flash back (row movement at table level) also after the entire DB restore?
Or on the DB restore it will erase the logs and cannot flashback before the restore time?

Thank you.
Oracle Database

Avatar of undefined
Last Comment
Chandan Pratihast

8/22/2022 - Mon
slightwv (䄆 Netminder)

I've never tried but as long as it is a normal restore to a point in time after the need to flashback, I would assume the flashback should still work.

If you want to flashback to say 8:00AM and you restore to 9:00AM, you should still be able to flashback.

If you open the database with resetlogs, you probably cannot flashback to a point before that time.

Again, never tried.  Might be an interesting experiment.  If you absolutely need to know:  I would set up a sample database and try it.
Chandan Pratihast

Thank you for reply, I will also try and let you all know.
Chandan Pratihast

Hi, I have tried and it seems after the restore the flashback table is not able to retrieve.
What I am trying "alter table <MYTABLE> ENABLE ROW MOVEMENT;" to try with one table.
Now is 11:00 AM 8-Aug. Restore done at 8:00 AM 8-Aug (today). Then flashback till 8-Aug 9:00AM is fine. But flashback 8-Aug 6:00AM gives below message:
ORA-01555: snapshot too old: rollback segment number 14 with name "_SYSSMU14_2473030277$" too small
flashback to 8-Aug 9:00 AM gives the data.

I think the retention period duration controlled by the below parameter (for row movement also?) am I right?
NAME                          TYPE    VALUE
----------------------------- ------- -----
db_flashback_retention_target integer 4320  

Thank you.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
slightwv (䄆 Netminder)

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Chandan Pratihast


I set the undo_retention to 24*3600 but the flashback does not show data beyond the restore time. Thanks anyway.