Delete vs Truncate in Oracle 9i 10g 11g – Difference and advantages

Delete Command features and advantages:
  • Command is for deleting the rows from the table.
  • Can delete selective rows from the table using where clause.
  • All rows can be deleted if no where clause is specified.
  • It needs a commit or rollback command to complete the transaction or make it as permanent change.
  • It is DML command.
  • Delete will generate redo log entries
  • Delete command use the Undo tablespace to rollback the transaction
  • Delete operation is slow compared to truncate command as it generates redo log entries.
  • Delete operation fires the delete triggers on that particular table.
  • You can grant delete table privilege to a user
  • Delete never de-allocate the space. It needs a table reorganization to claim the free space.
  • Delete doesn’t reset the high watermark

Truncate Command features and advantages
  • Truncate command removes the entire rows from the table
  • Truncate command cannot be used for selective deletes.
  • There is no rollback option with truncate command
  • Truncate is DDL command. Obviously implicit commit is followed by truncate
  • There will not be any undo generation so this will not use undo tablespace
  • Truncate does not generate redo log entries
  • Truncate is faster compared to delete command
  • Underling triggers will not get fired for truncate command
  • You cannot grant truncate table privilege to a user
  • Truncate de-allocate the space
  • Truncate makes unusable index usable again
  • You cannot flashback truncate command
  • Truncate reset the high watermark
DBA Tips Data Pump Reference


Post a Comment


dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google