| In previous releases of the database, when you issued the DROP command to drop a table, all contents of that table were permanently removed from the database. Now, with Oracle 10g Flashback Drop, a dropped table and its dependent objects (such as indexes, constraints, and triggers) are renamed to a system-generated string and logically placed in the recycle bin. Each recycle bin object will physically reside in the same tablespace and schema from which it was dropped. For example, if the DOCUMENTS table was created in the HIGH_GROWTH tablespace, then the dropped DOCUMENTS table will remain in the HIGH_GROWTH tablespace as long as the table remains in the recycle bin. Once a table is logically placed in the recycle bin, a user or DBA can recover it at a later time in case the table was dropped in error. This new flashback feature eliminates the need to perform a point-in-time recovery to recover a table that has been dropped, which would affect all database users. Flashback Drop is actually an extension of the Flashback Table command. Now you can issue the FLASHBACK TABLE .. TO BEFORE DROP command to recover the table from the recycle bin. You can also recover the dropped table from the recycle bin and provide a new name using the TABLE .. TO BEFORE DROP RENAME TO command. The Flashback Drop feature only supports tables and their dependent objects. Flashback Drop does not support data from the DROP TABLESPACE or DROP USER commands. Using Flashback Table with Table-Dependent Objects Usually, table-dependent objects such as indexes, triggers, and constraints should retain the original name after a dropped table has been recovered. However, the FLASHBACK TABLE ..BEFORE DROP command is used to recover a dropped table, all the indexes associated with the recovered table are recovered with a new system-generated name, not the original index name. Oracle is tracking this limitation as bug 3422568. |
Oracle Recycle Bin The recycle bin is actually a new data dictionary table (SYS.RECYCLEBIN$) that records information about dropped objects from your database. When objects are dropped, Oracle records all associated information needed to possibly recover the object at a later time in the recycle bin table. Tables and all depend objects are given a system-generated name with the prefix of BIN$. Users can also continue to access the data in the recycle bin and even perform actions with flashback query, but you must use the new system-generated name when accessing the data. Users will retain all writes and privileges to the recycle bin objects, just as they had prior to the DROP command. The recycle bin does not have any special storage requirements because all dropped objects will remain in the same tablespace they were dropped from, but it's worth noting that dropped objects still count against a user's quota. You can access the new recycle bin from the following data dictionary views: RECYCLEBIN USER_RECYCLEBIN DBA_RECYCLEBIN
The RECYCLEBIN Data Dictionary View RECYCLEBIN is actually a public synonym that is based on the view USER_RECYCLEBIN. USER_RECYCLE is a view that is based on the SYS.REYCLEBIN$ table. |
Dropped objects will not be logically removed from the recycle bin unless The object is manually purged from the recycle bin using the PURGE command. The tablespace that contains the dropped object runs out of space. A user's quota has been met. The tablespace that contains the dropped object needs to perform the autoextend operation.
Any tablespace that encounters space limitations will automatically purge recycle bin objects from oldest to newest. If required, Oracle will actually try to remove all table-dependent objects before removing the table itself. Also, if any tablespace has the autoextend option turned on, all recycle bin objects will be removed before the tablespace autoextends. To manually purge objects from the recycle bin, you can use the new PURGE command. The PURGE command can be used to manually remove one or all objects from the recycle bin, as well as recycle bin objects from specific tablespaces. SQL> purge recyclebin; SQL> purge dba_recyclebin; (Must by executed by SYSDBA) SQL> purge table hr.sales;; SQL> purge tablespace users;
You can also use the new PURGE command to drop tables and bypass the recycle bin all together. By using the following command, the table that is referenced is not entered into the recycle bin, and hence reverts back to pre-10g behavior: SQL> drop table emp purge;
Flashback Drop in Action You can use one of the following tools to flashback a table prior to the DROP command: Following is a brief example of using the FLASHBACK TABLE .. TO BEFORE DROP command via SQL: 1. | Detail the target table contents and drop the table:
SQL> select * from flashtest; ID ---------- 1000 2000 3000 4000 SQL> drop table flashtest; Table dropped. SQL> select * from flashtest; select * from flashtest * ERROR at line 1: ORA-00942: table or view does not exist
| 2. | Review the new entry in the recycle bin:
SQL> select object_name, original_name, ts_name, droptime from recyclebin; OBJECT_NAME ORIGINAL_NAME TS_NAME DROP_TIME ------------------------------ -------------------- -------------------- --------- BIN$yPOGdfEpQSqPLOx/PD26OA==$0 FLASHTEST USERS 2005-03-17:22:10:42
You can also use the show recyclebin command as follows:
SQL> show recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- FLASHTEST BIN$yPOGdfEpQSqPLOx/PD26OA==$0 TABLE 2005-03-17:22:10:42
| 3. | If needed, query the contents of the dropped table in the recycle bin:
SQL> select * from "BIN$yPOGdfEpQSqPLOx/PD26OA==$0"; ID ---------- 1000 2000 3000 4000
Querying Recycle Bin Objects It is required that you place quotes around the recycle bin object name because special symbols are used to rename the dropped object. |
| 4. | Recover the dropped table:
SQL> flashback table flashtest to before drop; Flashback complete. SQL> select * from flashtest; ID ---------- 1000 2000 3000 4000 SQL> select * from recyclebin; no rows selected
Using Flashback Table for Dropped Tables You may experience a time when you have multiple copies of the same table in the recycle bin. When using the FLASHBACK TABLE command-line syntax to retrieve a dropped table, make sure that the server retrieves the table that was dropped most recently from the recycle bin. If you needed to retrieve a table other than the most recently dropped table, you can use Enterprise Manager to select the appropriate table from the complete history. |
| 5. | Recover the dropped table with the RENAME option:
SQL> select * from flashtest; ID ---------- 1000 2000 3000 4000 SQL> drop table flashtest; Table dropped. SQL> select * from flashtest; select * from flashtest * ERROR at line 1: ORA-00942: table or view does not exist SQL> flashback table flashtest to before drop rename to flashtest1; Flashback complete. SQL> select * from flashtest1; ID ---------- 1000 2000 3000 4000
|
Along with SQL, you can also utilize the Flashback Drop feature via 10g Enterprise Manager. Following is a brief example using Enterprise Manager Flashback Drop: 1. | Under the Maintenance tab, select Perform Recovery under Backup/Recovery.
| 2. | Select Tables from the Object Type drop-down menu (see Figure 17.13).
Figure 17.13. Specifying recovery type from the 10g OEM Recovery wizard. 
| 3. | Select Flashback Dropped Tables as the operation type, and click Next.
| 4. | Enter the schema name and click Go. Under the Results heading, you will see all the tables listed in the recycle bin (see Figure 17.14). As you can see, there are multiple copies of the same table; you can choose to view the contents of and retrieve a specific table.
Figure 17.14. Specifying the correct table from the recycle bin from the 10g OEM Recovery wizard. 
| 5. | If you like, you can view the contents of each table in the recycle bin, as shown in Figure 17.15.
Figure 17.15. Viewing the contents from the recycle bin from the 10g OEM Recovery wizard. 
| 6. | Click the check box next to the table you want to recover from the recycle bin, and then click Next. On the Rename page, shown in Figure 17.16, rename the table as desired.
Figure 17.16. Renaming the dropped table within the recycle bin from the 10g OEM Recovery wizard. 
| 7. | After you enter the correct name for the table, click Next to go to the Review page, shown in Figure 17.17. Here you can review and submit your changes. When your changes are successfully made, a confirmation page will appear; click OK to return to the Maintenance page.
Figure 17.17. Table Recovery Summary from the 10g OEM Recovery wizard. 
|
Troubleshooting Flashback Drop The Flashback Drop feature works only for tables where you issue the DROP TABLE command and when its corresponding information is registered in the recycle bin. The Flashback Drop feature is not supported in the following situations: When you issue the DROP TABLE .. PURGE command. Here, the data will bypass the recycle bin and will be permanently removed. SQL> drop table flashtest purge; Table dropped. SQL> select * from recyclebin; no rows selected
When you issue the DROP TABLESPACE .. INCLUDING CONTENTS command. All data will bypass the recycle bin and will be permanently removed. Any recycle bin objects belonging to the tablespace are also purged. When you issue the DROP TABLESPACE command with active recycle bin objects. All recycle bin objects will be purged when this command is issued. When you issue the DROP USER .. CASCADE command with or without active recycle bin objects. With this command, all objects owned by this user will bypass the recycle bin and be permanently removed. Any recycle bin objects belonging to the user are also purged.
Disabling Flashback Drop Flashback Drop requires the functionality of the recycle bin to perform its work. In the event you ever want to disable the behavior of recycling, there is an undocumented parameter that you can set to disable it. The parameter _recyclebin controls the behavior of the database recycle bin. The default setting is TRUE, so setting its value to FALSE will disable the feature. SQL> alter system set "_recyclebin"=FALSE; System altered. SQL> conn stroupe/gwu Connected. SQL> create table flash as select * from sys.flashtest; Table created. SQL> drop table flash; Table dropped. SQL> sho recyclebin SQL> select * from recyclebin; no rows selected
|
