Truncate

From DBArtisan
Jump to: navigation, search

Go Up to Available object actions by DBMS

This action lets you quickly delete the rows of one or more tables or clusters. Truncating a table is a faster alternative to deleting all of its rows.

Caution: If you truncate a table, all the rows are deleted. These rows are not logged as individual drops and cannot be recovered from a transaction log or other type of log.

Platform Availability

To Truncate a Table or Cluster

  1. Initiate a Truncate or Truncate Table action against one or more tables or clusters. For more information see Initiating an object operation.
    The Truncate wizard/dialog box opens.
  2. For help with DBMS-specific settings and additional information, see the following topics:
  3. Click Execute. For information on the scheduling option, see Scheduling.

Notes on truncating Oracle objects

You can truncate any table or cluster in their schema or, if you have the DROP ANY TABLE system privilege, you can truncate any table in any schema.

Observe the following when truncating tables or clusters:

  • Before truncating a cluster or table containing a parent key, disable all referencing foreign keys existing in different tables.
  • Truncating a cluster or table does not generate any rollback information and commits immediately.
  • Oracle alters the storage parameter NEXT to the size of the last extent deleted from the segment.
  • Oracle automatically deletes all data in the table's indexes and any materialized view direct-load INSERT information associated with a truncated table.
  • If the table is not empty, all associated nonpartitioned indexes and all partitions of associated global partitioned indexes are marked unusable.
  • You cannot truncate a hash cluster nor can you truncate individual tables in a hash cluster or an index cluster.

When you truncate a table or cluster, you can specify whether space currently allocated for the table is returned to the containing tablespace or if it is returned to the system. The table below describes the Truncate dialog options available when truncating an Oracle table or cluster:

Option Description

Drop Storage

Select if you want the freed extents returned to the system where they can be used by other objects.

Reuse Storage

Select if you want the space to remain allocated to the table or cluster you have just truncated.

Notes on truncating Sybase ASE objects

Truncate against a Sybase ASE table can be applied to the entire table or to a single partition.

Note: You cannot truncate a table referenced by a foreign key constraint. Instead, use a DELETE statement without a WHERE clause.
Tip: When you truncate a table, Sybase ASE removes all rows from the target table, but retains the table structure (its indexes, columns, constraints, etc.). The counter used by an identity for new rows is reset to the seed for the column. To retain the identity counter, use a DELETE statement instead of TRUNCATE. To remove the target table definition and its data, use a DROP TABLE statement.

The following table shows the settings on the Truncate Table dialog:

Option Description

Partition Name (only available with a single table selected)

Selecting a partition name from the dropdown restricts the truncate operation to a single partition.

Notes on truncating IBM DB2 z/OS objects

When truncating an IBM DB2 z/OS table, the Action Options tab offers the following settings:

Setting Description

Reuse Storage

Corresponds to the DROP STORAGE/REUSE STORAGE clause of a TRUNCATE TABLE statement. This setting specifies whether storage currently allocated to the table is reused or dropped.

Restrict When Delete Triggers

Corresponds to the RESTRICT WHEN DELETE TRIGGERS/IGNORE DELETE TRIGGERS clause of a TRUNCATE TABLE statement. When enabled, an error is returned if triggers are defined for the table. When disabled, triggers defined for the table are not activated by the Truncate operation.

Immediate

Corresponds to the IMMEDIATE clause of a TRUNCATE TABLE statement. If enabled, the truncate operation is executed immediately and cannot be undone. If disabled, a Rollback can undo the Truncate operation.