Difference Between Drop And Drop Purge In Oracle

I am using Oracle Database and I am a bit confused about Drop and Purge Commands. In fact for me both does the same thing. Removes the t开发者_JS百科able with schema from database. What is the main difference between these two?

1. Drop Table Tablename;
2. Drop Table Tablename Purge;

Normally, a table is moved into the recycle bin (as of Oracle 10g), if it is dropped. However, if the purge modifier is specified as well, the table is unrecoverably (entirely) dropped from the database.

Oracle Database 10g introduces a new feature for dropping tables. When you drop a table, the database does not immediately release the space associated with the table. Rather, the database renames the table and places it in a recycle bin, where it can later be recovered with the FLASHBACK TABLE statement if you find that you dropped the table in error. If you want to immediately release the space associated with the table at the time you issue the DROP TABLE statement, then include the PURGE clause as follows. DROP TABLE employees PURGE; Specify PURGE only if you want to drop the table and release the space associated with it in a single step. If you specify PURGE, then the database does not place the table and its dependent objects into the recycle bin. NOTE: You cannot roll back a DROP TABLE statement with the PURGE clause, and you cannot recover the table if you drop it with the PURGE clause. This feature was not available in earlier releases.

A link to asktom article: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:32543538041420

Basically with oracle10, all dropped tables go into recycle bin from where they can be undropped. With purge you basically skip the recycle bin part and you drop the table without an option to undo the action.

SQL>drop table table_name;

This command deletes the table named table_name, but internally it is moved to recycle bin of oracle (which is just similar to deleting any file/folder using Delete key on windows os).
Benefits:
1. We will be able to restore the above deleted table if required.
Drawbacks:
1. Still occupies some amount of memory.

SQL>drop table table_name purge;

This command deletes the table table_name completely from the database (which is similar to deleting any file/folder using Shift + Delete key on windows OS).

The Benefits and Drawbacks will be vice-verse of the above.

The below statement will drop the table and place it into the recycle bin.

DROP TABLE emp_new;

The below statement will drop the table and flush it out from the recycle bin also.

DROP TABLE emp_new PURGE;

It’s clear from @Randy comment, just to add on:

(1) Drop Table Tablename:

Without purge, the table can be in the RECYCLEBIN or USER_RECYCLEBIN; which can be restored using the command FLASHBACK. This is similar to files we delete in our windows desktop, which move to the recycle bin, and can be restored back.

(2) Drop Table Tablename Purge:

If Drop is given along with Purge, its tablespace is released and cannot be restored. (Like Shift+Delete in desktop)

Following example gives practical example:

create table test_client (val_cli integer, status varchar2(10));
drop table test_client ;

select tablespace_name from all_tables where owner = 'test' and table_name = 'TEST_CLIENT';

SELECT * FROM RECYCLEBIN where ORIGINAL_NAME='TEST_CLIENT';

SELECT * FROM USER_RECYCLEBIN where ORIGINAL_NAME='TEST_CLIENT';

FLASHBACK TABLE test_client  TO BEFORE DROP;

select tablespace_name from all_tables where owner = 'test' and table_name = 'TEST_CLIENT';

drop table test_client purge;

select tablespace_name from all_tables where owner = 'test' and table_name = 'TEST_CLIENT';

drop table table_name purge;

By using this query what happened:- Normally, a table is moved into the recycle bin (as of Oracle 10g), if it is dropped.But still occupied the memory in database so to release the the memory for another object we can use the the Purge clause.