Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Hello
I have the following problem.
I have accidently dropped a column containing data from a table and would like to restore the table to the state it was in before I did this.
I have found a reference on the internet that states that you can use flashback to recover a table at a set point in the past even if you have dropped a column.
Flashback Table
Just as the flashback query helps to retrieve rows of a table, FLASHBACK TABLE helps to restore the state of a table to a certain point in time - even if a table structure change has occurred since then. The following simple command will take us to the table state at the specified timestamp:
SQL> FLASHBACK TABLE Employee TO
TIMESTAMP ('13-SEP-06 8:50:58','DD-MON-YY HH24: MI: SS');
Reference - http://www.orafaq.com/node/872
My DBA has sent me the following information;
Oracle Metalink note
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.
Can my DBA use flashback in this situation or not?
Ben