As we have been discussing Flashback technology, in this post, we shall see how it helps in getting a table recovered after being dropped.
A very common mistake that happens in the database environments is that someone would drop a table. Now that table drop is not a wrong command. Thus, if the user has appropriate privileges, he won’t get any error and table will be dropped. But how this table will come back, If it’s required. Now that’s where Flashback comes handy. Using Flashback, we can use Flashback –To before drop command to get the table recovered. The dropped table will be stored in a structure called Recyclebin. Basic part of recycle bin is that all what it contains is a renamed(yes) version of the table that’s just dropped. Now Recycle bin is something that’s automatically going to be purge that table if there is going to be a space pressure. But other than that, Recyclebin won’t drop the objects inside it by itself.
Let’s create a table and see how it’s recovered from Recycle Bin.
So the table is there and it’s created properly. Let’s drop this table and recover it from Recycle bin.
So the table is dropped. Looking at the recyecle bin, we can see that the table is renamed and is now given a system generated name. Table is still accessible but for only read-only purpose.
Now, let’s recover the table.
As we can see , once the table is recovered, it’s no longer available in the recyclebin.
If you want to drop the table without putting it in the recycle bin, you can use the command DROP with PURGE option. Then the table is permanently dropped.
It’s important to mention that if your object is stored in System tablespace, it won’t work.
Hope that helps.