Flashback Drop

The DROP TABLE PURGE command is used to drop objects. When this command is used, the objects are not placed in recycle bin and discarded permanently. This is similar to the drop feature in previous releases of Oracle database.

Information about all the objects that have been dropped can be viewed from USER_RECYLEBIN or DBA_RECYCLEBIN. The SQL Plus command SHOW RECYCLEBIN will give a list of all objects in the recycle bin.

Objects in the recycle bin remain there until permanently dropped with the PURGE command or recovered with the FLASHBACK TABLE <table_name> .. TO BEFORE DROP command. Similarly, objects remain in the recycle bin until there is no room for new rows or updates in the tablespace or until the tablespace has to be extended for tablespaces with the AUTOEXTEND ON option.

When an object is dropped and moved to the recycle bin, the name of the object is changed to avoid conflicts with objects in the recycle bin that has the same name. This also helps in creating a new object with the same name and dropping it again.

The tables in the recycle bin can be queried by issuing a SELECT command. Also, flashback query operations are possible on objects in the recycle bin, if the undo information is still available. It is not possible to INSERT, UPDATE or DELETE against tables in the recycle bin.