Monday, July 1, 2013

ORA-08189: cannot flashback row movement is not enabled

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);

9 comments:

  1. hi after enabling row movement its throwing error that table definition has changed
    what should i do?

    ReplyDelete
  2. I 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.

    ReplyDelete
  3. Thank You so much. It worked !! Cheers

    ReplyDelete
  4. Sir 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.

    ReplyDelete
    Replies
    1. Sir I'm using SQL Developer to perform above operations.

      Delete