Tonight as usual I was checking the Oracle blogs and popular OTN forum threads, Why the dropped table not in recyclebin? thread got my attention.
Especially after Oracle 10g below flashback features makes a big difference in Oracle DBAs’ and Developers’ lives;
• Flashback Database
• Flashback Drop
• Flashback Table
• Flashback Versions Query
• Flashback Transaction Query
Once upon a time I tried to summaries these features with my post; Oracle 10g Flashback Versions Query, Drop Table and Recyclebin Management But good things, especially new features, have their own alternative costs like bugs, limitations or restrictions which are usually lived and learned. These kind of information you can not find in the documentation or in the blog posts like mine above which usually try to give basic samples which are only designed to demonstrate the related features but not to stress them.
So lets start with some quick reminders; Flashback Table, Flashback Query, Flashback Transaction Query and Flashback Version Query are all based on undo(rollback) data which primarily provides read consistency and rolling back transactions in Oracle. But Flashback Database is based on before images in the flashback logs. And Flashback Drop is based on another 10g new feature called Recycle Bin which is there to manage dropped database objects until their space is needed to store new data. Each Flashback option has lots of interesting limitations or restrictions, I tried to summaries them from the related metalink notes, this place is like an unlimited gold mine for an Oracle man.
Note 249319.1 – Configure flashback database
Note 270060.1 – Use Flashback Table Feature and Resolve errors
Note 270535.1 – Restrictions on Flashback Table Feature
Note:435998.1 – What Do All 10g Flashback Features Rely on and what are their Limitations
Note 317499.1 – 10G Oracle Flashback Transaction Query – Introduction and usage
Note 369755.1 – Flashback Logs-Space management
Limitations of Flashback Database
• Flashback Database can only undo changes to a datafile made by an Oracle database. It cannot be used to repair media failures, or to recover from accidental deletion of datafiles.
• You cannot use Flashback Database to undo a shrink datafile operation.
• If the database control file is restored from backup or re-created, all accumulated flashback log information is discarded. You cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.
• When using Flashback Database with a target time at which a NOLOGGING operation was in progress, block corruption is likely in the database objects and datafiles affected by the NOLOGGING operation. If you expect to use Flashback Database to return to a point in time during an operation such as a direct-path INSERT, consider performing the operation in LOGGING mode.
• Shrinking a datafile or dropping a tablespace can prevent flashing back the Database
Limitations and Restrictions on Flashback Drop
• The recycle bin functionality is only available for non-system, locally managed tablespaces. If a table is in a non-system, locally managed tablespace, but one or more of its dependent segments(objects) is in a dictionary-managed tablespace, then these objects are also protected by the recycle bin.
• There is no fixed amount of space allocated to the recycle bin, and no guarantee as to how long dropped objects remain in the recycle bin. Depending upon system activity, a dropped object may remain in the recycle bin for seconds, or for months.
• While Oracle permits queries against objects stored in the recycle bin, you cannot use DML or DDL statements on objects in the recycle bin.
• You can perform Flashback Query on tables in the recycle bin, but only by using the recycle bin name. You cannot use the original name of the table.
• A table and all of its dependent objects (indexes (except for bitmap join indexes), LOB segments, nested tables, triggers, constraints and so on) go into the recycle bin together, when you drop the table. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together , It is possible, however, that some dependent objects such as indexes may have been reclaimed due to space pressure. In such cases, the reclaimed dependent objects are not retrieved from the recycle bin.
• Due to security concerns, tables which have Fine-Grained Auditing (FGA) and Virtual Private Database (VPD) policies defined over them are not protected by the recycle bin.
• Partitioned index-organized tables are not protected by the recycle bin.
• The recycle bin does not preserve referential constraints on a table (though other constraints will be preserved if possible). If a table had referential constraints before it was dropped (that is, placed in the recycle bin), then re-create any referential constraints after you retrieve the table from the recycle bin with Flashback Drop.
• When you drop a table, all materialized view logs defined on the table are also dropped but are not placed in the recycle bin. Therefore, the materialized view logs cannot be flashed back along with the table.
• When you drop a table, any indexes on the table are dropped and put into the recycle bin along with the table. If subsequent space pressures arise, then the database reclaims space from the recycle bin by first purging indexes. In this case, when you flash back the table, you may not get back all of the indexes that were defined on the table.
• You cannot flash back a table if it has been purged, either by a user or by Oracle Database as a result of some space reclamation operation.
• Oracle Database retrieves all indexes defined on the table retrieved from the recycle bin except for bitmap join indexes. (Bitmap join indexes are not put in the recycle bin during a DROP TABLE operation, so cannot be retrieved.)
Limitations and Restrictions on Flashback Table
• Flashback Table operations are not valid for the following type objects: tables that are part of a cluster, materialized views, Advanced Queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions.
• The following DDL operations change the structure of a table, so that you cannot subsequently use the TO SCN or TO TIMESTAMP clause to flash the table back to a time preceding the operation: upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (with the exception of adding a range partition).
• You cannot rollback a FLASHBACK TABLE statement. However, you can issue another FLASHBACK TABLE statement and specify a time just prior to the current time. Therefore, it is advisable to record the current SCN before issuing a FLASHBACK TABLE clause.
Limitations and Restrictions on Flashback Query
• Flashback Query is Not available after restarting the database.
• You cannot specify a subquery in the expression of the AS OF clause.
• You cannot use the VERSIONS clause in flashback queries to temporary , external tables, fixed tables, or tables that are part of a cluster.
• You cannot use the VERSIONS clause in flashback queries to views. However, you can use the VERSIONS syntax in the defining query of a view.
• You cannot specify this clause if you have specified query_name in the query_table_expression.
• Flashback Query does not undo anything. It is only a query mechanism. You can take the output from a Flashback Query and perform an undo yourself in many circumstances.
• Flashback Query does not tell you what changed. LogMiner does that.
• Flashback Query can undo changes and can be very efficient if you know the rows that need to be moved back in time. You can use it to move a full table back in time, but this is very expensive if the table is large since it involves a full table copy.
• Flashback Query does not work through DDL operations that modify columns, or drop or truncate tables.
• LogMiner is very good for getting change history, but it gives you changes in terms of deltas (insert, update, delete), not in terms of the before and after image of a row. These can be difficult to deal with in some applications.
ps: football is a very interesting game, I believe we played good at most only 30 minutes at the first 3 games and this was enough for this young team to the semi-finals. Czech game was one of the most dramatic games I have ever watched, we came back from 2-0 against Chelsea’s goal keeper Petr Čech.
Last world cup finalists France, last European Champion Greece or cradle of football England are not, but my country Türkiye is now in the first 8 and all 70 million are locked to the Croatia game. After that game if Germans let us we want Portugal back! :)