- How to bulk detach / attach databases in SQL Server - February 2, 2024
- How to enforce password policy in an Oracle database - 4 January 2024
- How do we change the name of the user / schema in an Oracle database - 1 December 2023
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:
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;