Another feature I wish SQL Server had.

I have been using Oracle databases since version 7.2 back in 1995, but starting in Oracle 10G released in 2003, they added a feature called the recycle bin. This feature does exactly what you think it would do. If you drop a table by accident you can recover the table and all of the data just like the Windows recycle bin is used to recover files.

Lets run through an example on how easy it is to recover a dropped table.

Here is a table called RATE_DIM full of data.

Now I drop it.

Now I try and query the table but it does not exist.

In Oracle there are views called: dba_recyclebin and user_recyclebin depending on who you are logged in as and what tables you want to recover. DBA gives you access to all dropped tables and USER gives you access to only yours.

If we query the view, you can see below that my table RATE_DIM is in the recycle bin. How do we get it out?

The syntax is easy if there is only one version of your table in the recycle bin.

FLASHBACK TABLE rate_dim TO BEFORE DROP;

Now we can query our table again and the recycle bin is now empty.

What happens if you have multiple drops of the table, for instance a batch job that creates and drops tables used for calculations?

The recycle bin now has an entry for each time the tables was dropped with the date/time it happened.

To restore a certain version of the table, we need to use a slightly more complex version of the flashback syntax.

FLASHBACK TABLE "object_name from view" TO BEFORE DROP rename to rate_dim

You need to put the object_name within double quotes (“) !!! I forget this often and get a syntax error.

To read more about this feature and other uses for it (purging, restoring dependant objects, etc), follow this link to the documentation.

https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables011.htm#ADMIN01511

This is a very useful feature, and I wish SQL Server had something like this out of the box by default.