This type has been available since the Oracle9i release, so that we can use it to query the database not for the data that currently exists, but for data that exists at a certain point in time in the past. This type is based primarily on the Undo Tablespace, so once the query is run, the information is searched for in the Undo Segments, this information that will be displayed to the user remains Temporary

And only available for the current Session, of course the Flashback Query process may fail if the required data is not found in the Undo Tablespace for example due to the long duration and rewriting new data

in the required data, so the database administrator must specify an appropriate Retention period.

Let us now imagine the following scenario to understand the Flashback Query process more clearly:

We now have a table called Employee that contains a set of fields.

SELECT * FROM EMPLOYEE;

The EMPLOYEE table contains 8 fields.

Let's assume that the user deleted some fields.

DELETE EMPLOYEE WHERE EMP_NO IN (1,5,7);

Now if we browse the table we will find only 5 fields.

What if we now make a query on the table but in a period of time in the past and let's assume that it was 20 minutes ago.

Of course it will search for this information in the Undo segments and display it, but if it does not find it an error message will appear.

SELECT * FROM EMPLOYEE AS OF TIMESTAMP

TO_TIMESTAMP ('23-06-08:03-33-38','DD-MM- YY:HH٤٢-MI-SS');

The query result will display 8 fields, but currently only 5 fields are available. So we have displayed information in the past, but what if we wanted to bring this information in the present, i.e. this information remains in the table, we can create a new table from the displayed information and then delete the Employee table and rename the new table to Employee1.

CREATE TABLE EMPLOYEE1 AS (SELECT * FROM EMPLOYEE AS OF TIMESTAMP

TO_TIMESTAMP ('23-06-08:03-33-38','DD-MM- YY:HH٤٢-MI-SS'));

Now we delete the Employee table.

DROP TABLE EMPLOYEE;

Then we rename the table EMPLOYEE1 to EMPLOYEE.

RENAME EMPLOYEE1 TO EMPLOYEE;

Now the EMPLOYEE table contains the information it had 20 minutes ago.

In the previous steps, we made a query in a specific Session about data at a specific moment in the past, but it is also possible to put all these Sessions in a specific moment in the past,

so that the result of all queries to the database through this Session is at a specific point in the past that is determined during the Flashback,

but the rest of the Sessions that work on the database The data is seen in the database in real time now; except for this session which sees the database at a specific moment in the past,

This is done through the DBMS_FLASHBACK package.

To return the current session to the past one full day we execute the procedure PROCEDURE ENABLE_AT_TIME found in the DBMS_FLASHBACK PACKAGE package.

DECLARE

QUERY_TIME TIMESTAMP;

BEGIN

QUERY_TIME := TO_TIMESTAMP(SYSDATE -1);

SYS.DBMS_FLASHBACK.ENABLE_AT_TIME ( QUERY_TIME);

COMMIT;

END;

/

Thus, during this session, all queries see the database as if we were yesterday.

It must be noted that through this pattern, DML operations cannot be performed except for SELECT.

This pattern can be canceled by the DISABLE procedure found in the DBMS_FLASHBACK package.

BEGIN

SYS.DBMS-FLASHBACK.DISABLE;

COMMIT;

END;

/

This is how we removed the Flashback mode.