Oracle Flashback Drop

• When you issue the DROP TABLE command, Oracle merely renames the table and   moves it to a recycle bin.

• The recycle bin is merely a data dictionary table that maintains information about   dropped tables.

• You can use the SELECT command to query the objects in the recycle bin. You can’t    use INSERT, UPDATE, and DELETE commands on these objects.

You can view the contents of the recycle bin by using either the DBA_RECYCLEBIN or USER_RECYCLEBIN.

Alternatively, you can use the SHOW RECYCLEBIN command which shows only those objects that you can undrop.

In order to restore a dropped table:

Note: When you flashback a table, Oracle will recover the dependent objects (except bitmap indexes) as well, but they will continue to have their cryptic system generated names.

If you drop and re-create a table with the same name, the recycle bin will have several versions of the dropped table, each with a unique system-generated table name.

If you then issue a FLASHBACK TABLE… TO BEFORE DROP command, Oracle will simply recover the latest version of the table. If you don’t want Oracle to do this, you have the following options:

o In the FLASHBACK TABLE command, provide the specific system-   generated name of the table you want to recover.

o Keep issuing the FLASHBACK TABLE command until you recover the    particular table you want.

DROP TABLE PERSONS PURGE
PURGE TABLE "BIN$Q1QZGCCMRSSCBBRN9IVWFA==$0"
PURGE TABLESPACE USERS USER SCOTT

PURGE RECYCLEBIN or PURGE USER_RECYCLEBIN will remove all objects belonging to the user issuing the command.

PURGE DBA_RECYCLEBIN command will remove all objects in the recycle bin. You must have the SYSDBA privilege to purge the entire recycle bin.

If you drop a tablespace, any objects belonging to the tablespace that are part of the recycle bin are purged immediately.

If you use the command DROP USER … CASCADE, any objects in the recycle bin that belong to that user are automatically purged.

• Table should belong to any non-SYSTEM, locally managed tablespace.

• Dependent objects can be in either a locally or dictionary managed tablespace, to be   stored in the recycle bin.

• The following types of dependent objects aren’t saved in the recycle bin:

o Materialized view logs
o Referential integrity constraints
o Bitmap join indexes

• You can’t save a table that has fine-grained auditing (FGA) or Virtual Private Database    policies defined on it.

Flashback table technology uses undo information to restore data rows in changed blocks of tables.

• You must have either the FLASHBACK ANY TABLE or the more specific FLASHBACK object privilege on the table you want to recover. In addition, you must have the SELECT, INSERT, DELETE, and ALTER privileges on the table.

• Make sure you enable row movement in the table: ALTER TABLE persons ENABLE ROW MOVEMENT

First, it is useful to note the current SCN then issue the command:

Oracle disables all relevant triggers by default and re-enables them upon completing the table recovery. You may simply append the ENABLE TRIGGERS clause to your FLASHBACK TABLE command if you want to override this default behavior.

The persons table continues to be online and accessible to users for all queries. However, Oracle acquires exclusive DML locks on the table during the Flashback Table operation.

It is important to note your current SCN before using a Flashback Table operation.

Use the FLASHBACK TABLE statement again to go back to just before you were when you issued the first statement.

• You can’t flashback a system or remote table.

• You can’t flashback a table back to a time preceding any DDL operation that changes    the structure of a table (for example, adding or dropping a column).

• Oracle doesn’t flashback statistics of the recovered objects.