Flashback Drop

---

Flashback Drop allows you to 'undrop' database objects. No longer will you have users desperate for the entire database to be restored to a point in the past because they thought they were on the dev instance instead of prod.

There's nothing all that dramatic about how Flashback Drop has been implemented. In Oracle Database 10g, when you drop a table, it merely gets renamed to a systemidentifiable string, but the segment remains in the tablespace it was dropped from. It will remain there until you undrop the object or purge it manually, or until the tablespace runs of out space for regular objects. If space pressure exists in the tablespace, Oracle will begin to age out dropped objects from oldest to newest.

When you drop an object, Oracle doesn't just rename the object itself. All dependent objects move to the Recycle Bin as well: indices, triggers, and constraints. Therefore, when you undrop the table, its entire dependent chain comes back with it.

The Recycle Bin

The Recycle Bin is a virtual directory of all dropped objects in the database: nothing more than a list of objects that have been dropped but not purged. The Recycle Bin is a logical container, and does not require a specific storage location-actual storage for all dropped objects is in the tablespace the object was in prior to drop. Take an example: user matt drops the table ws_app.woodscrews. The Woodscrews table is in the tablespace WS_APP_DATA, but its two indices are in the WS_APP_ IDX tablespace. When Woodscrews is dropped, the table is renamed to an internal name, and so are the two indices that existed on the table. Both appear in the DBA_ RECYCLEBIN view. However, the actual Woodscrews table segment still exists in the WS_APP_DATA tablespace, and the indices still exist in the WS_APP_IDX tablespace. They are logically part of the Recycle Bin, but physically exist in the same place they always have.

The Recycle Bin is quickly viewed via data dictionary views:

• USER_RECYCLEBIN

• DBA_RECYCLEBIN

Purging the Recycle Bin

Manually eliminating dropped objects from the Recycle Bin is not necessary. Objects are purged from the Recycle Bin as the space is required by other segments in the tablespace. In other words, dropped objects continue to take up space in a tablespace until other objects in that tablespace run out of free space elsewhere. Then, the first dropped object is the first object to be purged. Oracle automatically looks to purge indices before tables, so that actual data is the last thing to be lost. Recycle Bin objects will also be dropped before a tablespace autoextends, if autoextend is on.

The new PURGE command exists to purge the Recycle Bin. You can purge by user, by object, by tablespace, or the entire bin.

purge table sales.woodscrews; purge index sales.woodscrews_pk_idx; purge tablespace sales; purge recyclebin; 

How long do objects live in the Recycle Bin?

A valid question, but the answer of course is: it depends. No, really. It depends. The real question you probably want to hear is, can I control how long an object lives in the Recycle Bin? The answer to this question is no.

You cannot force an object to remain in the Recycle Bin, if space pressure exists in the tablespace of the dropped object. Even with autoextend on, the dropped object is purged before the TS extends. So, if you want to determine a certain lifespan on objects in the Recycle Bin, either you will be left to make the tablespace overly large to accommodate drops, or you can manually manage the Recycle Bin and purge those objects you don't want to keep in order to leave space for those you do.

You can, therefore, shorten the stay of an object in the Recycle Bin. But you cannot force something to remain, given a shortage of tablespace room.

Undropping Objects in the Recycle Bin

Getting objects back from the Recycle Bin is pretty simple-a simple SQL command renames the object back to its original name, along with any dependent objects.

flashback table ws_app.woodscrews to before drop; 

Of course, sometimes it's not that simple. For instance, if you have multiple dropped objects with the same name, then you would have to refer to the object by its new and improved Recycle Bin name.

SQL> select object_name, original_name, droptime, dropscn from user_recyclebin;  OBJECT_NAME                    ORIGINAL_NAME ------------------------------ --------------- DROPTIME               DROPSCN ------------------- ---------- RB$$48623$INDEX$0              PK_WOODSCREW 2004-01-12:15:21:26    1241651 RB$$48622$TABLE$0              WOODSCREW 2004-01-12:15:21:26    1241652 SQL> flashback table " RB$$48622$TABLE$0" to before drop;

Note the quotes around the Recycle Bin object name. These are required due to special symbols in the name.

If you have dropped an object and then created a new object with the same name, you can still flashback the first object. There is syntax in the Flashback SQL to rename the object when you pull it from the Recycle Bin.

flashback table ws_app.woodscrews to before drop rename to woodscrews_history;

HA Workshop: Explore Flashback Drop and the Recycle Bin

Workshop Notes

Time to drop our Woodscrew table and review the Recycle Bin contents. While this can be done at the command line, our workshop will show how to undrop a table using EM.

Step 1.  Drop the Woodscrew table. From EM | Administration | Schema | Tables, change the schema to the owner of the Woodscrew table. Select the Woodscrew table from the list of tables, and then click on the Delete button. A confirmation screen asks if you are sure about the drop, and you can click Yes.

Step 2.  View the list of tables. Note that Woodscrew is no longer in the list. Click on the Recycle Bin link in the lower right-hand corner of the screen. First, under Search, enter the Schema Name of the owner of the Woodscrew table and click on Go.

Note that in Figure 9-7 the table Woodscrew is in the Recycle Bin, along with the primary key index and the WOODSCREW_IDENTITY index. Also worth note is the button on the right titled View Content. This button allows you to look at the rows in the dropped table to determine if it is the correct object to be flashback dropped.

Figure 9-7: The Recycle Bin of user WS_APP

Step 3.  Click the radio button next to the Woodscrew table, and then click the Flashback Drop button. You will get a standard warning asking if you are sure.

Step 4.  The next page (see Figure 9-8) offers you the ability to rename the undropped object. After you click Next, you get the impact analysis from EM explaining what exactly Flashback Drop will do.

Figure 9-8: Impact analysis for flashback drop

Step 5.  Click on Submit. Confirmation tells us the selected tables have been restored from the Recycle Bin. You can confirm this by navigating back to EM | Administration | Schema | Tables and choosing the schema of the Woodscrew owner. The Woodscrew table will be back in place.

Step 6. Delete the Woodscrew table again. Click on Recycle Bin and find the dropped table again, as outlined in Step 2 above. Select Woodscrews, and then click on Purge (next to Flashback Drop).

Step 7. A confirmation screen asks you if you want to purge the selected objects and their dependents. Click on Continue. The Recycle Bin of the SALES user is now empty of the Woodscrew table and its indices. They have been permanently removed from the database.

---