This is a quick handy tip which I learned about over the weekend while working with an Oracle Database. Most of us work with MySQL Databases, which is what WordPress, Joomla and all the other content management systems uses. However MySQL is just one of many Databases that are available out there and as a developer we might need to work with these other databases.

Though the SQL commands are roughly the same among the different Database, they all have different features and work differently under the hood. And I discovered once just difference with the Oracle database over the weekend.

Since Oracle version 10, the database give a chance to flashback a dropped relational table.
Whenever a relational table is dropped, it goes into the recycle bin, which is similar to the recycle bin on your Mac or PC.

The feature needs to be turned out, which it is by default. It is done by setting the RECYCLEBIN initialization parameter to ON either at the system or session level.

Below is an example of how the recycle bin works.

SQL> CREATE TABLE test (column 1VARCHAR2(10), column2 VARCHAR2(10));

Table created.

SQL> INSERT INTO test VALUES ( ‘data 1’, ‘data 2’);

1 row created.

SQL> SELECT * FROM test;

columns1 column2
———- ——–
data 1     data 2

Now we are dropping the table (with RECYCLEBIN initialization parameter set to ON).

SQL> DROP TABLE test;

Table dropped.

SQL> SELECT object_name, original_name, type, can_undrop as “UND”, can_purge as “PUR”, droptime
2 FROM recyclebin;

OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME
—————————— ————- —– — ——————-
BIN$jKnYJpcmzqXgQAB/AQEaQg==$0 TEST TABLE YES YES 2010-07-31:15:31:34

Now we are going to use flash back and restored the table from the recycle bin.

SQL> FLASHBACK TABLE test TO BEFORE drop;

Flashback complete.

SQL> SELECT * FROM test;

columns1 column2
———- ——–
data 1     data 2

SQL> SELECT * FROM recyclebin ;

no rows selected

It is pretty useful especially if you have accidentally dropped a table or two and have already committed the changes.

There are two recycle bins, USER_RECYCLEBIN and DBA_RECYCLEBIN. And RECYCLEBIN points to USER_RECYCLEBIN.

As for emptying the recycle bin, you can use the following command below.

SQL> PURGE RECYCLEBIN;

Or if you want to delete a table but do not want it to the recycle bin (sort of like the shift-del command on a PC), use the following command

SQL> DROP TABLE test PURGE;

And if the table has primary or foreign keys, you need to cascade constraints.

SQL> DROP TABLE test CASCADE CONSTRAINTS PURGE;

You can read more about PURGE at Oracle’s Website

The problem I encountered over the weekend was that after dropping tables and committing the changes, the rows were still present in the USER_CONSTRAINTS table. The USER_CONSTRAINTS table is used to store the table relationships such as primary and foreign keys. The entries were deleted from the table but the table names in the table were being replaced with names starting with BIN$. I was baffled and it took an email to my university honors professor (My honors major was in databases)  to discover the reason.

Well that’s it, hope you had found this handy tip useful!