René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

truncate table|cluster [Oracle SQL]

truncate table table_name;
truncate cluster cluster_name;
A statement like delete from tablename deletes all records in the table, but it does not free any space (see On table sizes). In order to free the space as well, use truncate. However, a truncate can not be rolled back.
Basically, a truncate statement resets the high water mark to its initial position.
A truncate statement cannot be used on a synonym.
The truncate statement is not the same as the trunc statement.

Steps involved

Oracle executes the following steps for a truncate statement:
  1. Exclusive access to the segment header is requested.
  2. The segment header is locked
  3. All blocks within the to-be-truncated segment are flushed from buffer cache to disk.
  4. The free lists in the segment header block and in the free list groups blocks (if free list group blocks exist) are cleared. The changes (that is, the before image) hereof are recorded in a rollback segment.
  5. The segment is truncated. That is, the high water mark is reset. This change is recorded in the rollback segment as well.
  6. Extents beyond minextents are trimmed.
  7. In an OPS environment, the segment header blocks and the free list group blocks are flushed to disk.

Links