DROP TABLE … PURGE

The aim to use “drop table” command is to remove a table and all its data from the database. For example, let’s create a table named table_name :

SQL> create table table_name(id varchar(5));
Table created

If we decide to move the table:

SQL> drop table table_name;
Table dropped

But this execution moves the table, that we want to drop, to the recycle bin .We can see the contents of user’s recycle bin:

select * from recyclebin;

SQL> select object_name from recyclebin;
OBJECT_NAME
————————————————-
BIN$uCbv7uf/R/WSU6L3mWI1uQ==$0

After you drop the table Oracle will return an error:

SQL> select * from table_name;
select * from table_name
ORA-00942: table or view does not exist

If you accidentally drop the table, you can retrieve the oldest version of the table from the recycle bin:

SQL> flashback table table_name to before drop;
Done

SQL> select * from table_name;
ID
—–

But if you want to drop the table entirely and release the space, you should use “purge” clause. While , the table will be not moved to the recycle bin . You cannot recover the dropped table anymore.

SQL> drop table table_name purge;
Table dropped

On the other hand, you can use the following steps:

SQL> drop table table_name;
Table dropped

SQL> purge table table_name;
Done

Another way to purge table is:

SQL> drop table table_name;
Table dropped

SQL> select object_name from recyclebin;
OBJECT_NAME
————————————————–
BIN$KIkRl/BNTzmEdg9BW4Z3Pg==$0

SQL> purge table “BIN$KIkRl/BNTzmEdg9BW4Z3Pg==$0″;
Done