Flashback before and recyclebin

The Oracle 10g provides the ability to reinstating an accidentally dropped table, which is called Flashback Drop.

When a table is dropped, the database does not immediately remove the space associated with that table. Instead, the table is renamed to a system-defined name and placed in the Recycle bin. The flashback drop operations recovers from recycle bin.

SQL> DROP TABLE employee_tbl;

You can see the system-defined name of the table.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$gXxxELu7aQ/gQAoKd5l2Hg==$0 TABLE

You can see the dropped table in the recycle bin using
SQL> show Recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
-------------- ------------------------------ ------------ ----------------
EMPLOYEE_TBL BIN$gXxxELu7aQ/gQAoKd5l2Hg==$0 TABLE 2010-03-01:09:10:00

Recover the dropped table from Recycle bin using
SQL> FLASHBACK TABLE employee_tbl TO BEFORE DROP;

Recover the dropped table from Recycle bin with new name using
SQL> FLASHBACK TABLE employee_tbl TO BEFORE DROP RENAME TO employee_tmp;

You can also drop a table completely with out needing a flashback using
SQL> DROP TABLE employee_tbl PURGE;

The tablespace will not free up space until the table will remain until recycle bin is purged. You can purge table explicitly from recycle bin using
SQL> PURGE TABLE employee_tbl;

Purge all tables in recycle bin using
SQL> PURGE RECYCLEBIN;

As a DBA can purge all the objects in any tablespace using
SQL> PURGE DBA_RECYCLEBIN;

Purge all the objects in recycle bin in a tablespace using
PURGE TABLESPACE employee_tbs;

Purge all the objects in recycle bin in a tablespace specific to a user only using
PURGE TABLESPACE employee_tbs USER emp_user;

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Flashback Table Feature in Oracle 10g

In Oracle 9i Database, we have concept of Flashback Query option to retrieve data from a point in time in the past. The Oracle 10g provides the ability to recover a table or set of tables to a specified point in time in the past, this concept is called “Flashback table”.

Oracle Flashback Table operation is very quick and you do not have to restore any data from backups, and the rest of your database remains available while the Flashback Table operation is being performed.

The Flashback table depends on Undo information retained in the undo tablespace. If you set UNDO_RETENTION=1 hr, Oracle will not overwritten the data in undo tablespace until 1 hr. User can recover from their mistakes until specified time only.

Flashback table feature has some prerequisites:
•Row movement must be enabled on the table.
SQL> ALTER TABLE table_name ENABLE ROW MOVEMENT;
•You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
•You must have FLASHBACK ANY TABLE privilege or the FLASHBACK object privilege on the table.

Use below commands to restore the table to its state when the database was at the time specified by SCN or point in time.
SQL> FLASHBACK TABLE employee_tbl TO SCN 786;
or
SQL> FLASHBACK TABLE employee_tbl TO TIMESTAMP TO_TIMESTAMP ('2010-03-01 09:00:00', 'YYYY-MM-DD HH:MI:SS')

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com