The Secrets of Truly Dropping a Table in Oracle 10g!!

 Author: Carl Patrick

In Oracle10g you can restore the tables after being dropped. Actually when a table is dropped, we see it dropped but the table is renamed (moved to recycle bin) and we assume that it has been dropped.

Today we will discuss a great tip that will help you drop the table actually and completely, without needing a flashback feature.

Drop Table (without Purge):

Suppose you issue below command to drop a table in Oracle10g.

DROP TABLE MYTABLE;

Reinstating this table is easy as Oracle moves it to the recyclebin and the table is not truly dropped. We can use flashback feature to get it back.

FLASHBACK TABLE MYTABLE TO BEFORE DROP;

Drop Table (with Purge):

You can truly drop a table by using PURGE.

DROP TABLE MYTABLE PURGE;

Now the table is gone for ever and you cannot flashback to restore it.

FLASHBACK TABLE MYTABLE TO BEFORE DROP;
*
ERROR AT LINE 1:
ORA-38305: OBJECT NOT IN RECYCLE BIN

DROP TABLE RECYCLEMYTABLE PURGE; command does not rename the table to the recycle bin name. Instead the table is deleted permanently. A number of options are available that can help you manually control the recycle bin.

Purge Table from Recycle bin:

Below command will purge the MYTABLE from the recycle bin after its drop.

PURGE TABLE MYTABLE;

Purge Table from Recycle bin using Recycle bin name:

Below command will purge the MYTABLE from the recycle bin by using its recycle bin name. It will save space by removing MYTABLE and all dependent objects such as indexes, constraints etc. PURGE recyclebin; purges all objects in Recycle bin.

PURGE TABLE "BIN$08LhcpndanfgANIIIIIIIIIIIIIIPw==$0";

Drop index from Recycle bin:

You can also use below command to permanently drop an index from the recycle bin. Remember only the index will be removed and the copy of the table will be in the recycle bin.

PURGE INDEX MYINDEX;

Purge all objects in Recycle bin in a Tablespace:

Use below command to purge all the objects in recycle bin in a tablespace USERS.

PURGE TABLESPACE USERS;

Purge Recycle bin for specific user:

Use below command to purge all objects of the tablespace belonging to specified user.

PURGE TABLESPACE USERS USER SCOTT;

The SCOTT can clear his own recycle bin by issuing below command.

PURGE RECYCLEBIN;

Purge Recycle bin for specific user:

A DBA is able to purge all the objects in any tablespace by using below command. Remember only SYSDBA can issue this command.

PURGE DBA_RECYCLEBIN; Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Easy, Efficient & Quick Oracle Cloning!!
   Adding Custom Messages to Oracle Alert Log!!
   Oracle: A Guide to determine Best Execution Plan
   Efficient Tips for Oracle RAC Listener!!