How can we see historical image of a table in Oracle Database with Flashback Query
Certifications:
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
Many times we will need to see what entries the table had some time ago. We may still have to restore it from a wrong update or even drop. In Oracle this is easily done by using flashback query and flashback table.
How is it activated?
This feature is enabled from the start and how far back we can go depends on it undo retention.
To change to how many seconds the information will be kept on undo tablespace:
alter system set undo_retention = 86400 scope = both;
How to see table entries back in time (flashback query)
To see what entries the table had 1 hour ago:
select * from pinakas as of timestamp systimestamp - interval '1' hour;
And in another way:
select * from pinakas as of timestamp (systimestamp -1/24)
Accordingly if we wanted to see the records 20 minutes ago:
select * from pinakas as of timestamp systimestamp - interval '20' minute;
If we want to see what value it had before the last modification, with the following query we will find the date and time when each record was modified:
select SCN_TO_TIMESTAMP(ORA_ROWSCN), id from pinakas;
Then by putting the date and time we got from the previous query a little further back, we will see the value it had then:
select * from pinakas as of timestamp to_timestamp('2020-09-10 13:00:00','YYYY-MM-DD HH24:MI:SS');
How to restore deleted tables (flashback table)
Any painting is done drops it is not deleted directly but transferred for some time to recycle bin.
To see the deleted tables:
show recyclebin
To restore the table we deleted:
Attention if we have done it in addition to drop, purge it will have been deleted from the recycle bin as well.
flashback table pinakas to before drop;
To restore the table as it was 1 hour ago:
flashback table pinakas to timestamp systimestamp - interval '1' hour;
If it shows us the error ORA-08189: Cannot Flashback The Table Because Row Movement Is Not Enabled, then we run the following:
alter table pinakas enable row movement;
How can we keep the historicity of the painting forever
In case we don't want to fill it undo tablespace or in case we want to be able to keep the historicity of a table forever, we make one flashback archive.
To make the flashback archive in a specific tablespace:
create flashback archive rescue tablespace ts_tade retention 1 year;
To activate the flashback archive we made in a specific table:
alter table pinakas flashback archive rescue;
To turn it off:
alter table pinakas no flashback archive;
