It is probably the developers' responsibility to run tests, but they may not be aware of features in the database that makes the testing easier. Here the other day an ETL developer asked for help with resetting tables after ETL-testing. But there was no need to create complicated scripts to do so. I showed him flashback tables so he could easily fix it himself and run the tests over and over again without any pain.
If you haven’t tried this, it is a good case for Swingbench. The example below is from a round with the SOE benchmark.
Flashback tables is documented in Administrators Guide 20.10 , with a reference further to Backup and Recovery User’s Guide, 18.2
There are some requirements though. Materialized views, tables that are part of a cluster (table cluster, not RAC, you know), and other advanced stuff won’t work.
You need to set up a few things up before you start. Make sure you have room in your UNDO tablespace, and check the value for undo_retention_target. Documentation says that this limit is the minimal time Oracle attempts to preserve undo information before the space is recycled in the UNDO tablespace. Obviously you need to have enough space there for this to work, but you can monitor that as you go along.
Lastly, something called row movement must be enabled. That in a simple description means that Oracle can move a row and therefore give it another address (rowid). If you have some references to rowid’s in your code (which you shouldn’t have) you will not want to enable this. You enable row movement for a table called customers with this:
alter table customers enable row movement;Then you flashback a set of tables with this command.
flashback table ADDRESSES,CUSTOMERS,CARD_DETAILS,WAREHOUSES
, ORDER_ITEMS,ORDERS,INVENTORIES,PRODUCT_INFORMATION
,LOGON,PRODUCT_DESCRIPTIONS,ORDERENTRY_METADATA to timestamp to_timestamp('2017-06-26 16:10:00','yyyy-mm-dd hh24:mi:ss');The list above contains the tables involved in the SOE benchmark from Swingbench. After this you will receive:
Flashback succeeded.
Of course one can flashback the whole database, but this method doesn’t require the intervention of a DBA to perform it, nor the restart of the database with open resetlogs (scary stuff). Meaning the developer can do it as much as he wants without bothering you.
You may also try out what happens if you flashback only one or a few of the tables involved in the transactions being flashed back. It shouldn’t work. Why?