Toad World® Forums

Test the contents of a database table against a flashback query


#1

Hi,

We just started to use Code tester and after watching the video “Test the contents of a database table against a control/copy table” some of my teammates got sceptical about the practicality of creating a control/copy table. It’s due to the fact that the tables we use are quite large and even if you copy only the subset of data it could be very bulky. At that point I thought of using the flashback queries to achieve exactly the same test. The attached xml file contains a new test case “Delete some rows (flashback query)” which shows how to do it.

Regards,

Vadim
Q##QCTOD#DELETE_MULT_ROWS.xml (227 KB)


#2

Many thanks, Vadim. Investigating the use of FB query has been on my list for a while. I will check out your definition.

And, not to be terribly lazy, but can you tell me:

  • What version flashback query was made available in the DBMS? We still support 9iR2.

  • Is this a for-fee feature or does it come with the standard edition?

  • Is it something people have to install or enable? We try to avoid using Oracle features that are not generally available.

Regards, Steven


#3

Hi Steven,

This feature was made available in 9iR1 but you had to use dbms_flashback package to use it. Starting form 9iR2 as far as I know it became available just by using SELECT … FROM … AS OF TIMESTAMP

As far as I know this feature does not incur any additional license fees

You do not need to install the flashback separately because it’s available out of the box in Oracle Enterprise Edition. I am not sure about standard Oracle though. You just need to make sure that the database is running in ARCHIVELOG mode and the undo_retention parameter is set appropriately and UNDO tablespace is large enough to make sure the changes written during a test period will not be overwritten.

Regards,

Vadim