With Oracle Flashback Query and all the new derivatives of Flashback Query in Oracle Database 10g, dependency on the presence of undo can be critical. If you have a database that you will be performing frequent flashback operations on, then you might want to consider setting the UNDO Tablespace to guarantee the retention of all undo information until it has expired (as defined by the undo_ retention parameter). Note that if the UNDO Tablespace is set to guarantee retention of undo, then operations that need to generate undo will fail if sufficient undo area is not available, because Oracle Database 10g will not expire undo before its time when undo retention is specified.

You can use the retention guarantee parameter of the create UNDO Tablespace or alter UNDO Tablespace command, as shown in these examples:

-- Assumes OMF is configured.
Create UNDO Tablespace undotbs01
size 200m autoextend on
retention guarantee;
alter tablespace undotbs01 retention guarantee;

To reset an UNDO Tablespace so that undo retention is no longer guaranteed, use the alter tablespace retention noguarantee command.

The create database command also supports the retention guarantee clause. You can tell if you have put the UNDO Tablespace in retention mode through a query against the RETENTION column of the DBA_TABLESPACES view. Here is an example:

Select tablespace_name, retention from dba_tablespaces;

Flashback Transaction Query

Oracle Database 10g provides the ability to easily reconstruct SQL statements that have been previously executed by the database. Previously, you would need to use Log Miner to generate SQL redo statements that could be used to replicate SQL statements executed in the database. Now, Flashback Transaction Query can be used to reconstruct the SQL statements used to make changes in the database, and those that can be used to undo the change.

Flashback Transaction Query is supported through the new Oracle Database 10g view FLASHBACK_TRANSACTION_QUERY. The use of this view is dependent on the presence of undo, so the undo_retention parameter needs to be set in such a way as to preserve the undo that you will need. Likewise, you may want to configure guaranteed undo retention (as described earlier in this chapter) as well.

The following is an example of using the FLASHBACK_TRANSACTION_QUERY view. In this case, suppose that records got inserted into the mytab table in the last hour, and we want to remove all of those records. We can query the FLASHBACK_ TRANSACTION_QUERY view for all SCNs between 21553 and 44933 (we could use a timestamp as well) and, using the UNDO_SQL column, extract the SQL that will be required to undo those operations:

SELECT undo_sql FROM flashback_transaction_query
WHERE table_owner='ROBERT' and table_name='MYTAB'
AND start_scn between 21553 and 44933;

NOTE -- The indexing on the FLASHBACK_TRANSACTION_QUERY view seems to be lacking, and some queries (like the one in the example) can take some time to return.

Flashback Table

Restoring a table to a point in time different than that of the rest of the database can be a messy operation. If you are lucky, you will have an export from the point in time right before the data change. The other possibility is to use tablespace point in time recovery, but that is time consuming and messy. What if, instead, we could just flash back our table to the point in time we are interested in. Well, with Oracle Database 10g we can! Now, with the flashback table command, you can flash back an Oracle Database 10g table based on timestamp or database SCN. All flashback table operations must be at the beginning of any transaction, and flashback table operations are not supported for the SYS user. Here is an example:

SQL> Commit;
SQL> Flashback table emp to SCN 220360;
SQL> flashback table emp to TIMESTAMP
to_timestamp('2003-09-30 09:00:00',
     'YYYY-MM-DD HH:MI:SS');

To be able to flash back a table, the table must have row movement enabled via the alter table enable row movement command (row movement is disabled by default). Enabling row movement is something that can be done just before you issue the flashback table command, so you don’t need it enabled all the time. The flashback table command allows you to flash back or flash forward; thus, you can undo the effects of a previous flashback table command. You cannot flash back to a time prior to most DDL operations on the table being flashed back, or before the last time that the database was opened with the resetlogs command. Also, when you execute a flashback table statement, the execution of that statement is recorded in the alert log.

NOTE -- Since you can flash forward, it is a good idea to record the current SCN of the database before you flash back a table. The current SCN is available in the CURRENT_SCN column in the V$DATABASE view. Record the SCN before you issue the flashback tablecommand, not after.

This chapter is from Oracle Database 10g New Features, by Robert Freeman (McGraw-Hill/Osborne, 2004, ISBN: 0072229470). Check it out at your favorite bookstore today. Buy this book now.