Accidentally, important data was deleted. Of course not in a test system but during operation. The pulse and blood pressure rises. But do not worry – it is not always necessary to back up a backup. At least not if the error is noticed early. Crucial for this is the Flashback Query, which is based on the UNDO tablespace.
Undo tablespace
In the undo tablespace, the database stores modified data blocks. These “before” images are used to access already modified data records. However, this also means that the first user always makes changes to the database. A second user gets the data displayed by the Undo tablespace.
Changes are made in the database. Access to a locked data block is made via the Undo Tablespace.
When we commit, the undo tablespace is freed for overwriting. The correct values are already in the database. Rollback, on the other hand, restores the records from the undo tablespace to the database. This concept ensures that reading and writing users do not block each other and that every reading user sees the currently valid version.
Backup through the Flashback Query
For performance reasons, however, the undo tablespace is not deleted, but simply overwritten. This means that the data will persist until the buffer is used up. Thereafter, the database starts overwriting. A good value for the restoration is about 5-15 minutes.
The query
The query of a value from the past can be reproduced with the keywords “AS OF TIMESTAMP” and a date. However, the AS OF clause always refers to a table, so for a JOIN, an AS OF clause must be specified for each table. Below is a demonstrative example.
SELECT * FROM emp AS OF TIMESTAMP systimestamp - INTERVAL '5' MINUTE WHERE empno = 1234
If there is no image from the undo tablespace, Oracle returns an error. Now you really have to resort to a backup, journal entry or something else. The error is usually:
ORA-08180: No snapshot found based on given time