Flashback Table (10g)

From NazimWIKI
Jump to navigation Jump to search

The ability to flashback transactions to a point in time is quite a handy feature. Here is how it all works ....


First of all, the oracle init.ora parameter db_flashback_retention_target needs to be set. This parameter specifies the upper limit (in minutes) on how far back in time the database may be flashed back. The default value is 1440 minutes (24 hours).


How far back one can flash back a database depends on how much flashback data Oracle has kept in the recovery area.


To execute a FLASHBACK TABLE command, you need the FLASHBACK TABLE or FLASHBACK ANY TABLE privilege. In addition, you must have INSERT, DELETE and ALTER privileges on all the specified tables.


Retrieving a Table with Flashback Query

SQL> SELECT * FROM PAKISTAN_CRICKET;

FIRST_NAME           LAST_NAME           
-------------------- --------------------
Khan                 Imran               
Akram                Wasim               
Younis               Waqar               
Afridi               Shahid              
Miandad              Javed               

5 rows selected.

Drop the table

SQL> DR0P TABLE PAKISTAN_CRICKET;

Table dropped.

The default action associated with the drop command in 10g is to place the object in a recycle bin. You can view the contents in the recycle bin using ..

SQL> SELECT * FROM RECYCLEBIN;

Note: Each Schema has it's own recyclebin area - so to view the contents, you must be logged in as the Schema Owner of the object dropped. You may choose to permanently delete the table by purging it from the recyclebin or you could recover it via the following flashback command ..

SQL> FLASHBACK TABLE PAKISTAN_CRICKET TO BEFORE DROP;

Flashback successful.

Result ...

SQL> SELECT * FROM PAKISTAN_CRICKET;

FIRST_NAME           LAST_NAME           
-------------------- --------------------
Khan                 Imran               
Akram                Wasim               
Younis               Waqar               
Afridi               Shahid              
Miandad              Javed               

5 rows selected.

Refer to Oracle Documentation for other Flashback Table Options, such as by SCN, Timestamp etc.