It is also called Flashback Table Query and this type was introduced in Oracle 10g and is based mainly on the Undo Tablespace,

which is returning the table to a period of time in the past, benefiting from the information available in Segments Undo,

a number of modifications may have been made to the table, which we will find in the Undo Tablespace, of course the Flashback Table process may fail if the required information is not found in the Undo Tablespace as a result of the expiration of the period Keeping the required information, for example.

Here is the following scenario: Let us assume that we have two tables, the first is called DEPT, which is the departments table, and the second is called EMP, which is the employees table.

Let us add a new department to the DEPT departments table.

INSERT INTO DEPT VALUES(3,'FINANCE');

We add a new employee to the EMP employee table so that the new employee belongs to the new department.

INSERT INTO EMP VALUES(3,'KHALED'3);

Let's show the time now.

SELECT SYSDATE FROM DUAL;

Now we delete the two fields that we added to the two tables.

DELETE EMP WHERE EMP_NO=3;

DELETE DEPT WHERE DEPT NO=3;

Now let's make a Flashback Table for the EMP table to before deleting the field 3=EMP_NO.

FLASHBACK TABLE EMP TO TIMESTAMP

TO_TIMESTAMP('13-06-2023:00-00-00','DD-MM- YYYY:HH24-MI-SS');

The Flashback Table process may fail because we need to enable Row Movement, which is a Flag placed in the Data Dictionary that indicates to Oracle the modification operations.

ALTER TABLE EMP ENABLE ROW MOVEMENT;

We have done Enable Row Movement for the EMP table, now let's try to do Table Flashback for the EMP table again.

Another error message may appear, if the Constraint Key Foreign constraint is violated, as employee number 3 cannot be returned to the EMP table and he belongs to a deleted department,

He belongs to department number 3 that has been deleted, so what is the solution?

The solution is to create a Flashback Table for both DEPT & EMP tables.

But don't forget to enable Row Movement for DEPT table.

FLASHBACK TABLE EMP TO TIMESTAMP

TO_TIMESTAMP('13-06-2023:00-00-00','DD-MM- YYYY:HH24-MI-SS');

The Flashback Tables process has been completed successfully and it is not necessary to arrange the table in the Flashback statement because the Constraint constraints are verified after the end of the Transaction process. Note: If the attempt to retrieve the Flashback Table fails in any operation, the operation

stops and then the entire operation is rolled back, i.e. if the DEPT table recovery operation succeeded in the previous operation but the EMP table recovery operation failed, the entire operation will fail

and the entire operation will be rolled back.

As you have noticed, the Flashback Table process is not guaranteed to succeed, it may fail for one of the following reasons: -

1- If one of the Constraint Violated constraints is violated.

2- If Enable Row Movement is not done for the table that is required to be retrieved.

3- If the required information is not available in the Undo Tablespace,

no snapshot found based on specified time” ORA-08180

4- Flashback Table cannot be done for a table in Sys Schema.