How to use truncate in oracle

The TRUNCATE command in Oracle is used to quickly delete all data in the table. It does not use transaction logs, is fast, and cannot be rolled back. It will reallocate the table space, retain the table definition, and reset the identity column. Compared with DELETE, TRUNCATE is faster, cannot be rolled back, and space will be reallocated.

TRUNCATE Usage in Oracle

TRUNCATE is a command in Oracle that is used to quickly delete all data in a table. Unlike the DELETE statement, TRUNCATE does not use a transaction log, which makes it execute faster.

Syntax

<code>TRUNCATE TABLE table_name;</code>

Usage

TRUNCATE is usually used in the following situations:

• Quick Delete all data in the table
• Reset the identity column of the table
• Reallocate space for the table

Features

• Fast: TRUNCATE does not use the transaction log, so it is much faster than DELETE.
• No rollback: Once TRUNCATE is executed, the data will be permanently deleted and cannot be rolled back.
• Reallocate space: TRUNCATE frees the space used by the table so that it can be allocated to other tables or other objects.
• Preserve table definitions: TRUNCATE does not delete table definitions, such as constraints, indexes, or triggers.
• Reset the identity column: TRUNCATE will reset the table's identity column to its initial value.

The difference between DELETE

The difference between TRUNCATE and DELETE is as follows:

Features	TRUNCATE	DELETE
Speed	Faster	Slower
Can be rolled back	Not rolled back	Can be rolled back
Space allocation	Reallocate space	Do not reallocate space
Table definition	Reserved	Reserved
Identity identification column	Reset	Do not reset

##Example

The following example will delete all data in table

EMPLOYEES:

<code>TRUNCATE TABLE EMPLOYEES;</code>

Notes

Please note the following when using TRUNCATE:

TRUNCATE cannot be used with conditions, which means it will delete all data in the table.
• TRUNCATE is not rollable, so make sure you have a backup of the table data before executing it.
• TRUNCATE may cause tablespace fragmentation, so it is recommended to periodically refresh the tablespace if TRUNCATE is used extensively.

The above is the detailed content of How to use truncate in oracle. For more information, please follow other related articles on the PHP Chinese website!