Oracle 10g and dropping tables.

When dropping tables in Oracle 10g you find that they are not actually being dropped. This is a new feature where when a table is dropped it is put into a kind of recycle bin and appears like the example below.

OBJECT_NAME

--------------------------------------------------------------------------------
BIN$Fh4GKL2HF6HgRAADug5S0A==$0
BIN$Fh4HVxp+GAvgRAADug5S0A==$0

This is what is known as the flashback feature where you are able to retrieve lost data that has been accidentally dropped. There is more than one way of getting rid of these dropped tables. The first way is…to do nothing! The dropped tables do not release the table space, however once the table space is needed the tables in the recycle bin are deleted on a first-in first-out basis. The alternative is to manually purge the table, here is an example of how this is done:

PURGE TABLE "BIN$Fh4GKL2HF6HgRAADug5S0A==$0";

For more information on the flashback feature and dropped tables refer to
http://www.oracle.com/technology/pub/articles/10gdba/week5_10gdba.html

Click the link below to see
all KB articles by product.