Next year we are going to Oracle 10g on the back end. Currently production is using an Oracle 9i database. Our development database has been upgraded to 10g so we can be ready for next year. Developers have curiously found a bunch of tables in our schemas with names like “BIN$TktvMTcFRA3gRAADunAMgQ==$0”. However we are unable to drop these tables. A little research revealed that this is a new feature for Oracle 10g called the Recycle Bin.
I want to give a shout out to Natalka Roshah from Oracle FAQ. Her article explained the mysteries of the Recycle Bin to me. Apparently given the right 10g option being set, Oracle shall not actually drop a table when you issue the DROP command. Instead it renames the table so you can restore it easily in the future. A little research shows that the table does disappear from the USER_TABLES view. However it still exists in the USER_OBJECTS views with a type of “TABLE”.
Our project actually wants to truly drop the table when we issue a drop. So we will have our DBA team disable the Recycle Bin option. However for now we have resorted to issuing the following command to drop a specific Recycle Bin table:
PURGE TABLE “BIN$TktvMTcFRA3gRAADunAMgQ==$0”;
In my case, I wanted to get rid of all Recycle Bin tables in my schema. I had amassed 313 of them (which were taking up space). So I got rid of them all with the following command:
PURGE RECYCLEBIN;
The real funny thing about this is that some experienced developers who were not familiar with this Oracle 10g feature were sure these were not real tables. This included dudes who used to work for Oracle. I do not know about anybody else. But I think it is time for my company to send me to Oracle 10g training. Don’t you?
