Whenever I tried to Flashback Table in Oracle. The following error occurs.
FLASHBACK TABLE OUTWDCLR TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' minute);
Cause :
========================
Whenever you performed flashback operation(flashback table, flashback version query,flashback transaction query) of a table except flashback drop feature(which don't need to enable row movement as whole objects come from recycle bin) then above error will come.
Solution :
========================
Row movement of the table need to be enabled in order to perform flashback table.
See Row Movement Status
========================
SELECT TABLE_NAME,ROW_MOVEMENT FROM USER_TABLES WHERE TABLE_NAME='OUTWDCLR';
Enable Row Movement Status
========================
ALTER TABLE OUTWDCLR ENABLE ROW MOVEMENT;
Now You can FLASHBACK Your Table.
FLASHBACK TABLE OUTWDCLR
TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' minute);
FLASHBACK TABLE OUTWDCLR TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' minute);
Cause :
========================
Whenever you performed flashback operation(flashback table, flashback version query,flashback transaction query) of a table except flashback drop feature(which don't need to enable row movement as whole objects come from recycle bin) then above error will come.
Solution :
========================
Row movement of the table need to be enabled in order to perform flashback table.
See Row Movement Status
========================
SELECT TABLE_NAME,ROW_MOVEMENT FROM USER_TABLES WHERE TABLE_NAME='OUTWDCLR';
Enable Row Movement Status
========================
ALTER TABLE OUTWDCLR ENABLE ROW MOVEMENT;
Now You can FLASHBACK Your Table.
FLASHBACK TABLE OUTWDCLR
TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' minute);
Thank you sir
ReplyDeletethanks for sharing sir oracle fusion financials training
ReplyDeletehi after enabling row movement its throwing error that table definition has changed
ReplyDeletewhat should i do?
table add new column or constraints
DeleteThanks a lot. it's work.
ReplyDeleteI insert the data and then commit that part, when I was try to recover using this query -FLASHBACK TABLE a TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '17' minute); I got the result- Flashback succeeded.But when I was running the query data still persist.Please let me know the solution.
ReplyDeleteThank You so much. It worked !! Cheers
ReplyDeleteSir I've table 'A' with column as Id and value as 75, I perform update operation and changed the value to 59 it worked properly. But when I executed Rollback command and after checking the table 'A' it showed me Table as blank. What might be the reason. Please suggest.
ReplyDeleteSir I'm using SQL Developer to perform above operations.
Delete