Recently, I have been contacted several times by developers following manipulation errors that led to unwanted deletions of rows in a table on Oracle database. This can unfortunately happen frequently, especially on test or development environments.
The first thing that comes to mind is to restore a full backup of the database or perform a restore of the tablespace where the table is located (TSPITR) to retrieve the deleted rows. But there may be another faster solution if you do it quickly: using “Oracle Flasback Query“.
This feature can also be used to compare the content of a table with its content in the past, see the rows inserted/deleted/updated since a certain time,…
What is Oracle Flashback Query? How does it work?
Oracle Flashback Query allows you to see the contents of a table as it was several minutes/hours ago.
It is as simple as a simple “SELECT” on a table with a “magic clause”: AS OF TIMESTAMP.
In practice, Oracle will read the data as it was X minutes ago in your UNDO tablespace.
Contrary to what the name of the feature suggests, it has nothing to do with the “flashback database” feature!
In order to use this feature, there are still some constraints:
- Your database must be configure with “Automatic Undo Management” (parameter “undo_management” set to “AUTO”).
- The value of your “undo_retention” parameter must be high enough to cover the desired period. For example, if you want to query the table as it was 30 minutes ago, your “undo_retention” parameter must be set to at least 3600 seconds.
- For columns of type “LOB”, it depends on the value of the attribute “RETENTION” (see Oracle documentation about attributes for LOB columns).
- No DDL operation must have been performed on the table on which you want to use Flashback Query (TRUNCATE, DROP COLUMN, …).
- The database must not have been restarted in the meantime.
Note that due to Oracle’s management of UNDO segments, it is possible that the information is no longer present in the UNDO tablespace, even if the time limit defined by the “undo_retention” parameter is not exceeded. Oracle automatically deletes the UNDO segments when it is necessary to process new transactions.
How to use Flashback Query
For my examples, I will work on a table “MYDBAWOLRD” which contains 100 rows, with the following structure:
SQL> desc MYDBAWORLD
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(255)
REGION VARCHAR2(50)
COMPANY_ID NUMBER(10)
Recovering deleted rows
Following a mistake, I deleted all the rows of my table whose field “company_id” is equal to 10:
SQL> select count(*)
from mydbaworld
where company_id=10;
COUNT(*)
----------
10
SQL> delete from mydbaworld where company_id=10;
10 rows deleted.
SQL> commit;
Commit complete.
SQL> select *
from mydbaworld
where company_id=10;
no rows selected
15 minutes later, I realize my mistake… I now have several possibilities. For each possibilites, I can use the exact time I want to retrieve the content of my table, or give the delta (“15 minutes ago”).
I use the French date format in my queries “DD/MM/YYYY HH24:MI:SS”. Remember to adapt it according to your habits.
List rows deleted by mistake:
SQL> select *
from mydbaworld as of timestamp(to_timestamp('30/08/2021 13:50:00', 'DD/MM/YYYY HH24:MI:SS'))
where company_id=10;
NAME REGION COMPANY_ID
---------- ---------- ----------
Hunter NV 10
Imelda GA 10
Timothy WY 10
Dante CT 10
Chase MI 10
Rina IL 10
Evan WY 10
Maryam WI 10
Camden OK 10
Hiroko KS 10
10 rows selected.
Which is equivalent to:
SQL> select *
from mydbaworld as of timestamp(systimestamp - interval '15' minute)
where company_id=10;
Create a copy of the MYDBAWOLRD table as it was 15 minutes ago:
SQL> create table mydbaworld_bck as select * from mydbaworld as of timestamp(systimestamp - interval '15' minute) where company_id=10;
Table created.
SQL> select count(*)
from mydbaworld_bck
where company_id=10;
COUNT(*)
----------
10
Reinsert deleted rows directly:
SQL> insert into mydbaworld (select * from mydbaworld as of timestamp(systimestamp - interval '15' minute) where company_id=10);
10 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from mydbaworld where company_id=10;
COUNT(*)
----------
10
Determines which rows have been deleted
In most cases, you will not know which “delete” statement has been executed. In this case, you just need to adapt the SQL of the Flashback Query to recover the deleted rows:
SQL> select *
from mydbaworld as of timestamp(to_timestamp('30/08/2021 14:10:00', 'DD/MM/YYYY HH24:MI:SS'))
where (name, region, company_id) not in (
select * from mydbaworld
);
The query is not very pretty because I had not defined any unique constraint on the table. Otherwise, the “NOT IN” clause could have been limited to a single column.
As in my first example, you can store them in a temporary table, or reinsert them directly:
SQL> insert into mydbaworld (select * from mydbaworld as of timestamp(to_timestamp('30/08/2021 14:10:00', 'DD/MM/YYYY HH24:MI:SS')) where (name, region, company_id) not in (select * from mydbaworld));
18 rows created.
SQL> commit;
Commit complete.
Determines which rows have been inserted
The idea is the same as for a deletion, except that this time the order of the tables in the query is inverted:
SQL> select *
from mydbaworld
where (name, region, company_id) not in (
select *
from mydbaworld as of timestamp(to_timestamp('30/08/2021 14:30:00', 'DD/MM/YYYY HH24:MI:SS'))
);
Once again, you can store them in a temporary table, or delete them directly:
SQL> delete from mydbaworld where (name, region, company_id) in (select * from mydbaworld where (name, region, company_id) not in (select * from mydbaworld as of timestamp(to_timestamp('30/08/2021 14:30:00', 'DD/MM/YYYY HH24:MI:SS'))));
18 rows deleted.
SQL> commit;
Commit complete.
As you can see from these examples, Flashback Query is a really powerful feature that allows you to fix most of the “little mistakes” that can happen on development platforms (at least, I hope so).
It can save you from having to restore a database or a tablespace with the downtime that this can cause.
The key point is to be quick, and not to modify the table you want to work on.
Most common errors
The most common errors encountered when working with Flashback Query are the expiration of data in the UNDO tablespace (because it was sized too small, or the “undo_retention” was exceeded):
SQL> select * from mydbaworld as of timestamp(to_timestamp('30/08/2021 12:00:00', 'DD/MM/YYYY HH24:MI:SS'));
select * from mydbaworld as of timestamp(to_timestamp('30/08/2021 12:00:00', 'DD/MM/YYYY HH24:MI:SS'))
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 20 with name "_SYSSMU20_77666022$" too small
Or when DDL operations have been performed against the table between now and the time specified in the flashback query:
SQL> truncate table mydbaworld;
Table truncated.
SQL> select * from mydbaworld as of timestamp(to_timestamp('30/08/2021 13:50:00', 'DD/MM/YYYY HH24:MI:SS'));
select * from mydbaworld as of timestamp(to_timestamp('30/08/2021 13:50:00', 'DD/MM/YYYY HH24:MI:SS'))
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
I hope you find this post useful. Stay tuned for more DBA stuff!
