17-25. Flashback Versions Query Considerations
The VERSIONS clause cannot be used to query
- External tables
- Temporary tables
- Fixed tables
- Views
The VERSIONS clause cannot span DDL commands
The VERSIONS clause in a SELECT statement cannot produce versions of rows across the DDL statements that change the structure of the corresponding tables. This means that the query stops producing rows after it reaches a time in the past when the table structure was changed.
What if I want to flashback version query after a DDL? is it possible?
Segment shrink operations are filtered out
Certain maintenance operations, such as a segment shrink, may move table rows across blocks. In this case, the version query filters out such phantom versions because the row data remains the same.
17-9. Flashback Database Limitations
- You cannot use Flashback Database if the control file has been restored or re-created.
- Data file dropped after Flashback target time cannot be recovered, i.e. dropped during the span of time you are flashing back. In this case, the dropped data file is added to the control file and marked offline, but it is not flashed back)
- Flashback Database cannot flash back a data file to a time after its creation and before the resize operation. If a file was resized during the span of time to which you are going to flash back the database, then any such data files must be taken offline before performing the flashback operation. This is applicable for files that are shrunk rather than expanded.
For all above situations, you should use an incomplete recovery operation to return the database to a specific time.
The above is also explained at Administration Worshop II 6-24.
17-7. Flashback Database: Reducing Restore Time
With Flashback Database, the time to recover a database is now proportional to the number of changes that need to be backed out (and not to the size of the database) because you do not have to restore data files.
The Oracle database periodically logs “before images” of data blocks in the Flashback Database logs. Block images can be reused to quickly back out the data file changes to any time at which flashback logs are captured just before the desired target time. Then, changes from the redo log files are applied to fill in the gap (since we only have periodic images so the gaps are filled with redo log).
Flashback Database can be applied to NOLOGGING operations such as direct load inserts (Administration Workshop II 6-29).
An alternative to FLASHBACK DATABASE is to create a Guaranteed Restore Point (Administration Workshop II 6-29). The latter does not require flashback logging to be enabled in your database and can return your database to its state at the specified SCN:
SQL> CREATE RESTORE POINT before_load 2> GUARANTEE FLASHBACK DATABASE;
7-24. Dropping a table
Dropping a table removes:
- Data
- Table structure
- Database triggers
- Corresponding indexes
- Associated object privileges
Optional clauses for the DROP TABLE statement:
- CASCADE CONSTRAINTS: Dependent referential integrity constraints are removed as well.
- PURGE: No flashback possible
Some considerations:
If you do not use the PURGE option, the table definition, associated indexes, and triggers are placed in a recycle bin. That means that the space taken up by the table and its indexes still counts against user’s allowed quota for the tablespaces involved. Use PURGE RECYCLEBIN command to empty the recycle bin or FLASHBACK TABLE command to recover schema objects if PURGE clause was not used in DROP TABLE statement
