Wednesday, March 14, 2018

Oracle Database: DROP, TRUNCATE, DELETE Operations

Oracle Database Drop and Truncate are DDL  (Data Definition Language) Command while Delete is DML (Data Manipulation Language) Command. We will learn about each of them in detail below,

Drop:   Drop oracle command is used to remove complete table from database. As drop is DDL so commit operation not required after drop query.

Syntax : Drop Table Table_Name [Purge]; 
Table_Name can be any table name that you want to delete from database. Purge is optional keyword.

Drop Table have purge option. Purge is used to delete table permanently from database. If you have used purge with drop table then that table directly deleted from database can not recovered if required.Table delete with drop and purge will not stored into recycle bean.

Truncate :  Truncate Oracle Command is used to remove all rows i.e. all record from table. This command not delete table definition from database.Truncate is DDL command so commit not required after truncate oracle table. Truncate is faster than Delete. Where clause can not be used with Truncate command.

Syntax : Truncate Table Table_Name

Delete : Delete Oracle Command is used to remove all, single, multiple rows i.e. all record from table. This command not delete table definition from database.Delete is DML command so commit required after Delete command to save changes permanently.  Delete can be used with where clause.
Delete is time consuming and slow compared to truncate.

Syntax :  Delete Table Table_Name; Commit;

Delete multiple Rows/Records base on some criteria:
Syntax : Delete from Table_Name [where Condition]

Click Here for Oracle Database Insert,Select,Update & Delete Table Operations.


Post a Comment