Register now or log in to join your professional community.
<p>Drop statement can have below two syntax. What is the difference between them.</p> <p>1. drop table <table_name> ;</p> <p>2. drop table <table_name> purge;</p> <p> </p>
Oracle Database10g 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.
1) This command deletes the table named table_name,
2) This command deletes the table table_name completely from the database
purge is an additional clause you can use while dropping the table to remove the space taken by the table or related index in recycle bin.
you can use the below query to see the recyclebin details
select * from RECYCLEBIN;
Hi,
As per my knowledge :
drop table <table_name> ; --> This command would drop the table but the said table could always be recovered as it would be residing in the recycle bin.
2. drop table <table_name> purge;
Using this command the table would be completely removed from the database and we would not be able to recover it from the Recycle bin. The said command would free up the space from the database.
Using purge option with drop table will drop the table and release the tablespace immediately. Table dropped using purge option,cannot be recovered.
Normally, a table is moved into the recycle bin (as of Oracle10g), if it is dropped. The table is unrecoverable (entirely) (dropped from the database) if purge syntax is used.
