Description: Oracle 10G has enhanced the flashback feature further and has turned it into a much more powerful feature by introducing a number of additional function. Some of the more common ones are discussed here.
- Flashback Table
- Flashback Drop
- Flashback Database
Language: PL/SQL Highlight Mode: PLSQL Last Modified: March 13th, 2009
Just like the flashback query helps retrieve rows of a table,
FLASHBACK TABLE helps restore the state of a table to a certain
point in time even if a table structure changed has occurred since
then. The following command will take us to the table state at the
FLASHBACK TABLE EMPLOYEE TOTIMESTAMP('15-SEP-08 8:50:58','DD-MON-YY HH24: MI: SS');/*
This command will not only restore the tables, but also the
associated objects like indexes, constraints etc.
Oracle 10g introduces the function "Flashback drop". If a DROP
TABLE command has been issued for the table EMPLOYEE, we can
still restore the entire table by issuing the following command:
FLASHBACK TABLE EMPLOYEE TO BEFORE DROP;-- Recovering a dropped table doesn't any easier than this!/*
Flashback Database requires the creation and configuration of
an Oracle Flash Recovery Area before this feature can be used.
"Flash Recovery Area", created by the DBA, is the allocation of
space on the disk to hold all the recovery related files (Flashback
Logs, Redo Archive logs, RMAN backups, and copies of control files).
Use the initialization parameters db_recovery_file_dest and
b_recovery_file_dest_size to set the destination and the size
of the recovery area.
Set Flashback to enabled to make Oracle database enter the
flashback mode. The database must be mounted as Exclusive and
not open. The database also has to be in the ARCHIVELOG MODE
before we can use this feature:
*/ALTER DATABASE ARCHIVELOG;-- start the database in EXCLUSIVE mode:
STARTUP MOUNT EXCLUSIVE-- enter flashback mode:ALTER DATABASE FLASHBACK ON;-- issue the Flashback command and rewind the database to -- the state it was in one hour ago.
Flashback database TOTIMESTAMP sysdate-(1/24);-- after the system comes back with FLASHBACK COMPLETE, -- open the database:ALTER DATABASE OPEN RESETLOGS;-- the database is now restored. -- note that we have the option -- of using SCN instead of timestamp.
SQL University.net courses meet the most demanding needs of the business world for advanced education
in a cost-effective manner. SQL University.net courses are available immediately for IT professionals
and can be taken without disruption of your workplace schedule or processes.
Compared to traditional travel-based training, SQL University.net saves time and valuable corporate
resources, allowing companies to do more with less. That's our mission, and that's what we deliver.