Flashback Table and Flashback Query in Oracle

Back to: Oracle DBA Tutorials

Flashback Table and Flashback Query in Oracle

In this article, we will learn about the Flashback Query and Flashback Table in Oracle with Examples. Please read our previous article discussing the Categories of UNDO in Oracle with examples. Then, let’s create a table and start learning about Flashback.

Step 1: Create a table.

We have connected to the JAYAPBD pluggable database, which has built-in objects. Let’s check the employees table.

Statement: select * from employees;

We now have the details for the employees table. Let’s create a table using the select * from employees.

create table emp_copy3
as
select * from employees;

So, the table is created. While creating the table with select, all the constraints will not be copied; only the constraints that are not null will be created. Let’s check the contents of the table emp_copy3.

Statement: select * from emp_copy3;

We have all the present data; same as employees.

Step 2: Drop the table.

Let’s go ahead and drop the table to see how things work. The drop statement is a DDL statement, and it is auto-committed.

Statement: drop table emp_copy3;

So, the table has been dropped. Let’s now check its contents again or check whether it exists.

Statement: select * from emp_copy;

We can see the output, as the table or view doesn’t exist. So, the table is now dropped. The objects that were dropped will be present in the recycle bin, which was introduced in Oracle 9i. Let’s check the recycle bin.

Statement: select * from recyclebin;

We can see the dropped object, its details, and its drop time. We can also see the DROPSCN. SCN indicates the System Change Number.

The SCN is an internal maintained y the database management system to log changes made to a database. Whenever an application commits a transaction, the log writer process writes records from the redo log buffers in the SGA to the online redo logs on disk. LGWR also writes the transactions SCN to the online red log files. When we make any transaction, oracle provides SCN.

Oracle doesn’t write the data to the data files directly; it stores it in the redo log files, which affects the database’s performance. Not every time we make changes to the database, it does not write into the data files and gives us an SCN number. The SCN number during the drop time is 10228642. We can retrieve the table back from the recycle bin using the flashback.

Statement: flashback table emp_copy3 to before drop;

We have used the flashback table with the table name before drop, which describes that we are asking the Oracle database to get the table back to before drop. The flashback has now succeeded. As the flashback has now succeeded. Let’s check the table now.

Statement: select * from emp_copy3;

The data in the table is back to normal. Let’s now check the recycle bin.

Statement: select * from recyclebin;

So, the recycle bin is empty. We can even empty the recycle bin using a purge.

Statement: purge recycebin;

Now, let’s learn about the Flashback Version Query. This Flashback version query helps to retrieve the different versions of specific rows that existed during a given time interval. A row version is created whenever a commit statement is executed. Let’s take an example and find out about the flashback version query. Let’s take an employee_id as 107 and check the salary details.

Statement: select salary from employees where employee_id=107;

We can see the salary for the employee is 4200. Let’s try to update the employees table fort the emp 107.

update
employees
set salary=salary+100
where employee_id=107;

So, the updated salary value for the employee is 107. Let’s commit the table.

A new SCN will be triggered in the database as we have committed the data.

select salary from 
employees
where employee_id=107;

So, the salary has been updated from 4200 to 4300. If we want to check the previous value for the employee’s old salary, we can use the Oracle flashback query to get the previous value.

select versions_starttime,versions_endtime, salary from 
employees
versions between scn minvalue and maxvalue
where employee_id=107;

From the above value, we can see the versions_starttime, versions_endtime, and salary. In the query, we have to mention the versions between the scan min value and the max value. We have many options for using the Oracle flashback query. One of the options is scnminvalue and max value. Let’s execute the statement.

From the output, we can see that the current salary is 4300, and the previous value is 4200. The version_endtime is null, so the record is still valid. Now, let us try another update and see if changes were recorded.

update
employees
set salary=salary+100
where employee_id=107;
commit;

So, we are updating the salary of the same employee but 100. We have committed the transaction after the update statement.

So, the table is now updated. Let’s try to check the flashback query again.

select versions_starttime,versions_endtime, salary from 
employees
versions between scn minvalue and maxvalue
where employee_id=107;

We can see the new salary is 440, and the version end time for the value 4300 is mentioned. If the version end time is null, it is the latest value. If we execute the same flashback query, we may not be records after one year. This depends on many factors.

• Tablespace type (Fixed or Auto Extend)
• The UNDO RETENTION period
• Retention Guarantee

The tablespace for this pluggable database is Auto, and the UNDO_RETENTION is 900 seconds. All these values will be available for 50 minutes. However, if you run the same flashback query after 50 minutes, you will not find them.

In the next article, I will discuss the Automatic Tuning of Undo Retention in Oracle. In this article, I explain Flashback Table and Flashback Query in Oracle with examples. I hope you enjoy this Flashback Table and Flashback Query in the Oracle article.