Beware Deleted Tables September 22, 2009

From version 10 onwards, Oracle introduced the concept of the recycle bin and the purge command. I won’t go into details of the recycle bin, go to Natalka Roshak’s FAQ page on it if you want the details.

I am only concerned with the fact that from Oracle 10 onwards by default if you drop a table or partition it is not dropped but just renamed, unless you use the PURGE command {which can be tricky if you are using a 9i client as PURGE won’t be accepted by SQL*Plus}.

And it keeps catching me out. So I’m blogging about it – more for me than any of you lot :-)).

How exactly does it catch me out? Well, as an example, I’m looking at some partitioning considerations at the moment and ran the below.

How can that be? How can you have more records with partition information than tables with PARTITIONED flag set to YES?

{And for readers of my post yesterday on querying the data dictionary, check which of the statements performs better – one might expect the view specific to partitions to be quicker than one that is not, but then the partition-specific view is looking at a lot more under the covers}.

Errr, no Neil, not dropping things on Production. I tend to trial things and do development on a separate database – I like to call it “DEV”. It saves all that tedious impact on the live system than developing code on Live can introduce. I’d highly recommend it to you…
Can I have my 15% now? Or over the next couple of months at a reasonable interest rate? :-) {OK, I’ll settle for a beer at the MI SIG next week}.

I can’t turn off the recyclebin on live in this case anyway. We let managers play on there with OLAP and database management tools they have downloaded off the net….

On Googling a bit more, I found the answer to my question.. The column was required in early releases of 10gR1, when the deleted tables still showed up in DBA_TABLES. This was fixed in 10.1.0.3 and 10.2.0.1.