• Home
  • Archive by category "Oracle"

Oracle TRUNCATE TABLE

Source: https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10007.htm#SQLRF01707

Caution:You cannot roll back a TRUNCATE TABLE statement, nor can you use a FLASHBACKTABLE statement to retrieve the contents of a table that has been truncated.

Use the TRUNCATE TABLE statement to remove all rows from a table. By default, Oracle Database also performs the following tasks:

  • Deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter
  • Sets the NEXT storage parameter to the size of the last extent removed from the segment by the truncation process

Removing rows with the TRUNCATE TABLE statement can be more efficient than dropping and re-creating a table. Dropping and re-creating a table invalidates dependent objects of the table, requires you to regrant object privileges on the table, and requires you to re-create the indexes, integrity constraints, and triggers on the table and respecify its storage parameters. Truncating has none of these effects.

Removing rows with the TRUNCATE TABLE statement can be faster than removing all rows with the DELETE statement, especially if the table has numerous triggers, indexes, and other dependencies.

See Also: