You must have heard
about the time machine in movies or magazines, just think of an oracle
providing the same feature with its databases, and that is called a
flashback.
We can go back in
time to query data from the table, of course, there are certain limits to it,
but it’s possible, we will learn this with an example.
Here is the table
employee, with 8 rows
SQL> select count(*) from
employee;
COUNT(*)
----------
8
and this particular
snapshot is captured at the below timings
SQL> select sysdate from dual;
SYSDATE
-------------------
2022-07-21 23:37:12
Now, deleted a couple
of rows using the below query
SQL> delete from employee where
emp_id>305;
2 rows deleted.
SQL> commit;
Commit complete.
now we can see number of
rows in the table
SQL> select count(*) from
employee;
COUNT(*)
----------
6
so basically, 2 rows
are deleted from the table, what if you want to read those 2 deleted rows, you
have the option to use the flashback query option, below is the exact format to
achieve it
Check the number of
rows based on timestamp, as we saw we had 8 rows preset in the table at 23:37 but
later we deleted them, based on this timing, check the number of rows
SQL> select count(*) from employee
as of timestamp TO_DATE('2022-07-21 23:37:12','YYYY-MM-DD HH24:MI:SS');
COUNT(*)
----------
8
see here, we were
able to fetch data based on the flashback option, this is just reading, what if
you want to insert data back to the table, use the below commands
SQL> insert into employee select *
from employee as of timestamp TO_DATE('2022-07-21 23:37:12','YYYY-MM-DD
HH24:MI:SS') where emp_id>305;
2 rows were created.
SQL> commit;
Commit complete.
now check the count
in the table, you will be surprised to see, that data is inserted back to the
table.
SQL> select count(*) from
employee;
COUNT(*)
----------
8
0 comments:
Post a Comment