Thursday, January 14, 2010

Difference Between Truncate table and Drop table

Truncating a Table

TRUNCATE TABLE hr.employees;

• Truncating a table deletes all rows in a table and releases used space.
• Corresponding indexes are truncated.
• All rows in the table are deleted.
• No undo data is generated and the command commits implicitly because TRUNCATE
TABLE is a DDL command.
• Corresponding indexes are also truncated.
• A table that is being referenced by a foreign key cannot be truncated.
• The delete triggers do not fire when this command is used.

Dropping a Table

DROP TABLE hr.department

 When a table is dropped, the extents used by the table are released. If they are contiguous,they may be coalesced either automatically or manually at a later stage.
 The CASCADE CONSTRAINTS option is necessary if the table is the parent table in a foreign key relationship.

No comments:

Post a Comment