Flashback Database Views - Oracle

Oracle Database 10g offers several views that are associated with database flashback:

• V$DATABASE

• V$FLASHBACK_DATABASE_LOG

• V$FLASHBACK_DATABASE_STAT

V$DATABASE View - The V$DATABASE view has a new column associated with it for Flashback Database called FLASHBACK_ON. This column is a Boolean value that indicates whether or not Flashback Database is enabled.

V$FLASHBACK_DATABASE_LOG View - The V$FLASHBACK_DATABASE_LOG view is a new view in Oracle Database 10g that allows you to monitor the Flashback Database retention target. This view can help you to estimate the amount of space in the recovery area that will be required for the flashback workload. This view contains columns that allow you to query the following:

• The lowest Flashback Database SCN or time (OLDEST_FLASHBACK_SCN and OLDEST_FLASHBACK_TIME columns)

• The retention target time (RETENTION_TARGET column)

• The current size of the flashback data, in bytes (FLASHBACK_SIZE column)

• The estimated size of flashback data that you need for your current target retention (ESTIMATED_FLASHBACK_SIZE column)

V$FLASHBACK_DATABASE_STAT View - The V$FLASHBACK_DATABASE_STAT view can be used to monitor the overhead of maintaining the flashback data in the Flashback Database logs. From this view, you can estimate the total amount of space that will be required for future Flashback Database operations.

Flashback Database Limitations

Some limitations to Flashback Database exist. You cannot use Flashback Database to flash back a database to a point in time before the following operations occurred:

• The database control file has been restored or re-created

• The object that you need to query belongs in a tablespace that has been dropped

• The database datafile that contains the object to be queried has been shrunk

• A recovery through the resetlogs command has occurred

As an example, suppose that you drop the TOOLS tablespace and re-create it and the objects within it. You cannot flash back the database to a point before you dropped the TOOLS tablespace and successfully query objects in that tablespace (though you can query objects in other tablespaces at that time). You can, however, flash back the database to a point-in-time after you re-created the TOOLS tablespace, and perform a query against any changes that occurred in that tablespace after it was re-created.

Flashback Drop

Oracle Database 10g offers Flashback Drop. This feature allows you to undo the effects of a drop table command via the new flashback table SQL command using the to before drop syntax. This section looks at the Flashback Drop of a table via the flashback table command. First, it introduces you to the recycle bin. Then, it shows how to undrop tables with the flashback table command. Finally, it describes the data dictionary views associated with Flashback Drop.

NOTE -- You do not need to enable Flashback Database in order to use this feature.

Recycle Bin

To support Flashback Drop, Oracle Database 10g introduces the concept of a recycle bin. So, now when a table is removed, it is moved to the recycle bin and remains there until the recycle bin is purged. Thus, a statement like drop table parts causes the parts table to be moved to the recycle bin. If you want to drop the parts table and not have it get moved into the recycle bin, use the new purge parameter of the drop table command:

SQL> Drop table parts purge;

Note that objects that are dropped and moved into the recycle bin do not have their extents deallocated, so they will continue to take up space in your database until you purge them from the recycle bin or free space is consumed, in which case Oracle Database 10g will purge older objects from the recycle bin automatically. Use the purge table command to purge objects from the recycle bin, as shown in this example:

Purge table rb$$44022$table$0;

You can also use the purge tablespace command to purge all objects in the recycle bin associated with a specific tablespace, as shown in this example:

Purge tablespace all_users;

Note that this command will not remove objects in the tablespace (not already in the recycle bin) or drop the tablespace itself.

You might have noticed in the purge table command example that the command referenced a rather odd-looking object name, rb$$44022$table$0. The reason for this odd name is that Oracle Database 10g renames objects when they are moved to the recycle bin, to avoid naming conflicts. The naming convention takes the form

rb$$OBJ#$$ObjectType$Version

where:

• OBJ# is the dictionary object number of the object

• ObjectType is the type of the object. This might be table, normal index, or bitmap index.

• Version makes sure the object name is unique.

Flashback Drop Data Dictionary Views

You can find a list of objects in the recycle bin, and their associated object names, through a query against the USER_RECYCLEBIN view. Here is an example of a query against this view that demonstrates the relationship between the object name of the objects in the recycle bin and the old database objects:

SELECT object_name, original_name, type
from user_recyclebin;

What’s really cool about the recycle bin is that you can continue to query an object after you have dropped it! You simply query the object based on its new object name, as shown in this example:

Select * from rb$$44022$table$0;

The flashback table to before drop Command

The benefit of the recycle bin is that it enables you to recover a table that you have dropped. To do this, you issue the flashback table command using the to before drop parameter:

SQL> flashback table mytab to before drop;

This is a very easy way to recover your table from the recycle bin. If you have created and dropped the same object multiple times, then the one most recently dropped will be recovered. You can use the recycle bin name for the table if you prefer, which allows you to restore the specific version of the table that you wish to restore. Also, you can use the rename to clause to rename the table that is being recovered as seen in this example:

SQL> flashback table mytab to before drop rename to test;

Once the table is recovered by the flashback table command, it is removed out of the recycle bin (though other, older versions of that object may still exist in the recycle bin).

When you retrieve a table from the recycle bin, all associated indexes will be retrieved as well, except bitmap join indexes, which are not saved in the recycle bin after a drop table operation. If the recycle bin starts running out of space, Oracle Database 10g will remove index entries before it removes table entries. Thus, flashback table operations might not restore all indexes.

Triggers and constraints are restored as well except foreign key constraints. Note that all restored, table-related objects will be restored with their recycle bin names, rather than their original names. So, you might want to make a note of the original names before you do the restore. Also, materialized views (Mviews) that are dependent on the tables being dropped are dropped and are not saved in the recycle bin, so they are lost forever.

Flashback Versions Query

Can you conceive of a case where you would like to see all the versions of data for a given row over a given point in time? Perhaps you would like to see how much a specific employee’s salary has changed over the last month for auditing purposes. Oracle Database 10g offers Flashback Versions Query for just such an occasion. With this feature, you can define a minimum and maximum time (using the versions between timestamp clause of the select statement) or scn range (using the versions between scn clause of the select statement), and then query a specific row or set of rows in a table to see a list of all values assigned to those rows.

Jonathan Says… Don’t forget to ask your application suppliers, the ones who create and drop temporary tables all over the place, how they plan to handle the side effects of this feature. It’s a good thing that items in the recycle bin will be purged automatically on an “out of space” condition.

Here is an example of using Flashback Versions Query. In this example, we want to look at a list of employee salary values and how they have changed between 30 minutes ago and 1 minute ago.

SELECT ename, sal FROM emp
 VERSIONS BETWEEN TIMESTAMP
   SYSTIMESTAMP - INTERVAL '30' MINUTE AND
   SYSTIMESTAMP - INTERVAL '1' MINUTE
   WHERE empno=22;

Note that the changes that are shown are only committed changes (changes committed or rolled back). Also note that you cannot use the versions between clause of the select statement when querying a view, but this clause can be used within the definition of a view. Also, the ability to use Flashback Versions Query is dependent on the availability of undo records, which also implies that it’s dependent on the setting of the undo_retention parameter. If the time or SCN listed in the BETWEEN clause represents a point in time beyond the undo_retention parameter setting, then an error will be returned.

Flashback version query requires that the flashback and select privileges be granted to any nonprivileged user who you wish to allow to use flashback version queries. Also, if you will be using Flashback Versions Query or other Oracle flashback features frequently on specific objects, you might want to consider another new feature, Guaranteed Undo Retention, which is the next topic.

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.

>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus