The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.
TRUNCATE removes all rows from a table. The operation cannot be rolled back. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. The operation cannot be rolled back.
DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
From Oracle 10g a table can be "undropped". Example:
SQL> FLASHBACK TABLE emp TO BEFORE DROP;
Flashback complete.
Above answer was rated as good by the following members: darrenttl
RE: what is the difference between delete,drop and tru...
DELETE: when u r using delete command in oracle the data will be deleted in the table. after that if u r using rollback the data in the table will be retrived.TRUNCATE: in the case of truncate when u r using truncate what ever data in the table it will be deleted. after that if u r giving rollback also the data will not be retrived. but the structure of the table is available.DROP: in the case of DROP if u r using drop the rollback command is not working and structure and data in the table also deleted. nothing willbe available about that table.EXAMPLE:DELETE: delete * from emp; then the data inthe table will be deleted after this u give ROLLBACKthen the data willbe retrieved. TRUNCATE: truncate table emp; then the data inthe table willbe deleted but the sturucture is available> if u type--- desc empthen it give the structure of that table.DROP: drop table emp:after that what ever command u r using on emp table it will give error. bcuz the table object emp is not available in database
RE: what is the difference between delete,drop and tru...
The delete statement will result in the table spaces or memories would remain as the current size and table structure remain in the database. Delete is not effective when you use it to remove all data from a table because it takes up storeage spaces due to unclear memories. The truncate statement will result in clearing table spaces or memories and the table structure remain in the database. Therefore it free table storage spaces and only use it when you need to remove all data from a table. The drop statement will result in completely removing the table from the database. Note: Statements above are made with the assumption that a commit is has been executed. Once a commit statement is executed (commit is final). Therefore rollback a transaction will not work if commit statement is executed.
RE: what is the difference between delete,drop and tru...
Delete command: It actually removes the data permanently from the table with user specified conditions until you commit your work.It you want to save you could rollback the command. It will also create the space in the tablespace which in turn in a datafile.Drop command : It will remove both the structure and data in the specified table and could not be rolled back as it is a DDL command.Truncate : It will remove full data from the table without any user specified condition. It cannot be rolled back.
RE: what is the difference between delete,drop and tru...
Hi
I dont think there is any possibility of rolling back a truncated transaction
through rollback command ...if requires one can recover it through other ways
either via a point in time recovery using incomlete recovery...but as per oracle syntax rollback is concerned no body can recover a truncated tables data through rollback command.
See Delete Deletes the Tables data either selective or all and put the data in an rollback segment as well where from a read consistent view or a flashback view can be seen even after commit and one can rollback the tables data after delete as well. one more thing delete doesn' resets the HWM and space occupied with insert can't be reclaim by delete.when you deletes table data indexes are not dropped.Its a DML statement
Drop drops all tables with its data structure idexes and integrity constraints.
Truncate command removes all the data from table and drops all integrity constraints its an DDL Statement and resets the HWM but you can't rollback a tables data after truncate and one more thing truncate doesn' remove selective rows it can only removes the full tables data.truncate leaves the database structure ad is a good tool to reset HWM.but a dangerous tool for database security.
RE: what is the difference between delete,drop and tru...
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.
TRUNCATE removes all rows from a table. The operation cannot be rolled back. As such TRUCATE is faster and doesn't use as much undo space as a DELETE.
The DROP command removes a table from the database. All the tables' rows indexes and privileges will also be removed. The operation cannot be rolled back.
DROP and TRUNCATE are DDL commands whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone) while DROP and TRUNCATE operations cannot be rolled back.
From Oracle 10g a table can be undropped . Example:
RE: what is the difference between delete,drop and tru...
Delete : A DML Statement creates a undo log . deletes record from table . Can be rolled back.
Truncate : A DDL it actually sets the high water mark level of the table back to zero (depends on MINEXTENTS too) hence no one can read the table records.The commend get itself issues a commit as it is DDL command hence can not be rolled back . Unlike delete truncate can not be selective . Table defination remains as such. It is fast as no logs are maintained.
DROP:drops a table completelty (with table structure)can not be rolled back . can be cascaded.
RE: What is the difference between delete,drop and tru...
I dont know about Oracle. But acccording to sql server
DELETE TABLE is a logged operation so the deletion of each row gets logged in the transaction log which makes it slow.
TRUNCATE TABLE also deletes all the rows in a table but it won't log the deletion of each row instead it logs the deallocation of the data pages of the table which makes it faster. Of course TRUNCATE TABLE can be rolled back.
1 
0