4.5.1 Specification format and rules for the TRUNCATE TABLE statement

The TRUNCATE TABLE statement deletes all the rows in a base table.

Organization of this subsection

(1) Specification format

TRUNCATE TABLE-statement ::= TRUNCATE TABLE table-name

table-name

Specifies the name of the base table whose rows are to be deleted (the row deletion target table ). For the rules on specifying a table name, see (2) Table name specification format in 6.1.5 Qualifying a name.

Note that the following tables cannot be specified:

Note: If an archivable multi-chunk table is specified, the data stored in chunks that are archived and not archived is deleted.

To execute the TRUNCATE TABLE statement, both of the following privileges are required:

During the execution of the TRUNCATE TABLE statement, the DB area is locked in exclusive mode. Therefore, you cannot execute the TRUNCATE TABLE statement while performing operations on another table or index stored in the DB area that contains the table to be processed, or the index of the table to be processed.
If the TRUNCATE TABLE statement terminates successfully, a COMMIT statement is automatically executed before the transaction terminates. Therefore, there is no need to execute a COMMIT statement after the execution of the TRUNCATE TABLE statement.
If both of the following conditions are met, the TRUNCATE TABLE statement will result in an error.
- There is a cursor performing retrieval from the table targeted by the TRUNCATE TABLE statement in the connection where the TRUNCATE TABLE statement is executing
- The cursor is open
If both of the following conditions are met, the TRUNCATE TABLE statement will result in an error.
- The TRUNCATE TABLE statement is executed using the JDBC driver
- There is a Statement object or a PreparedStatement object performing retrieval from the table targeted by the TRUNCATE TABLE statement within the same connection

Example

Delete all rows in the sales history table (SALESLIST).

TRUNCATE TABLE "SALESLIST"