{"id":1988,"date":"2020-11-02T07:00:00","date_gmt":"2020-11-02T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=1988"},"modified":"2024-07-01T17:52:05","modified_gmt":"2024-07-01T14:52:05","slug":"pos-mporoyme-na-doyme-enan-pinaka-ti-pe","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-mporoyme-na-doyme-enan-pinaka-ti-pe\/","title":{"rendered":"How can we see historical image of a table in Oracle Database with Flashback Query"},"content":{"rendered":"

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<\/strong> and flashback table<\/strong>.<\/p>\n\n\n\n

How is it activated?<\/h2>\n\n\n\n

This feature is enabled from the start and how far back we can go depends on it undo retention<\/strong>.<\/p>\n\n\n\n

To change to how many seconds the information will be kept on undo tablespace<\/strong>:<\/p>\n\n\n\n

alter system set undo_retention = 86400 scope = both; <\/pre>\n\n\n\n
How to see table entries back in time (flashback query)<\/h2>\n\n\n\n
To see what entries the table had 1 hour ago:<\/p>\n\n\n\n
select * from pinakas as of timestamp systimestamp - interval '1' hour; <\/pre>\n\n\n\n
And in another way:<\/p>\n\n\n\n
select * from pinakas as of timestamp (systimestamp -1\/24)<\/pre>\n\n\n\n
Accordingly if we wanted to see the records 20 minutes ago:<\/p>\n\n\n\n
select * from pinakas as of timestamp systimestamp - interval '20' minute; <\/pre>\n\n\n\n
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:<\/p>\n\n\n\n
select SCN_TO_TIMESTAMP(ORA_ROWSCN), id from pinakas;<\/pre>\n\n\n\n
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:<\/p>\n\n\n\n
select * from pinakas as of timestamp to_timestamp('2020-09-10 13:00:00','YYYY-MM-DD HH24:MI:SS');<\/pre>\n\n\n\n
How to restore deleted tables (flashback table)<\/h2>\n\n\n\n
Any painting is done drops<\/strong> it is not deleted directly but transferred for some time to recycle bin<\/strong>. <\/p>\n\n\n\n
To see the deleted tables:<\/p>\n\n\n\n
show recyclebin <\/pre>\n\n\n\n
To restore the table we deleted: <\/p>\n\n\n\n
Attention if we have done it in addition to drop, purge it will have been deleted from the recycle bin as well.<\/em><\/p>\n\n\n\n
flashback table pinakas to before drop; <\/pre>\n\n\n\n
To restore the table as it was 1 hour ago:<\/p>\n\n\n\n
flashback table pinakas to timestamp systimestamp - interval '1' hour;<\/pre>\n\n\n\n
If it shows us the error ORA-08189: Cannot Flashback The Table Because Row Movement Is Not Enabled<\/em>, then we run the following:<\/p>\n\n\n\n
alter table pinakas enable row movement;<\/pre>\n\n\n\n
How can we keep the historicity of the painting forever<\/h2>\n\n\n\n
In case we don't want to fill it undo tablespace<\/strong> or in case we want to be able to keep the historicity of a table forever<\/strong>, we make one flashback archive<\/strong>.<\/p>\n\n\n\n
To make the flashback archive in a specific tablespace<\/strong>:<\/p>\n\n\n\n
create flashback archive rescue tablespace ts_tade retention 1 year;<\/pre>\n\n\n\n
To activate the flashback archive we made in a specific table:<\/p>\n\n\n\n
alter table pinakas flashback archive rescue; <\/pre>\n\n\n\n
To turn it off:<\/p>\n\n\n\n
alter table pinakas no flashback archive; <\/pre>\n\n\n\n
Sources:<\/h2>\n\n\n\n