In Oracle, the TRUNCATE statement is used to quickly remove all rows from a table while preserving the table structure. Unlike the DELETE statement, which removes rows one by one and generates undo and redo logs, TRUNCATE is a DDL (Data Definition Language) statement that deallocates space used by the table and its associated objects, such as indexes and triggers, without generating undo and redo logs.
Here's the syntax of the TRUNCATE statement in Oracle:-
TRUNCATE TABLE table_name;
- table_name: The name of the table from which you want to remove all rows.
When you execute the TRUNCATE statement, Oracle removes all rows from the specified table, and the table's storage space is released back to the tablespace. However, it's important to note the following considerations:-
1. Cannot Rollback: Unlike the DELETE statement, which can be rolled back using a ROLLBACK command, TRUNCATE cannot be rolled back. Once you truncate a table, the operation is irreversible, and the data is permanently deleted.
2. Reset Identity Columns: If the table has any identity columns (e.g., GENERATED BY DEFAULT AS IDENTITY), truncating the table resets the identity column sequence to its initial value.
3. Referential Integrity: Truncating a table does not fire any triggers or check referential integrity constraints, such as foreign key constraints. Therefore, you must ensure that truncating a table does not violate any integrity constraints in your database schema.
4. DDL Operation: Since TRUNCATE is a DDL operation, it acquires exclusive table-level locks, preventing concurrent DML (Data Manipulation Language) operations (e.g., INSERT, UPDATE, DELETE) on the table until the `TRUNCATE` operation completes.
Overall, TRUNCATE is a fast and efficient way to remove all rows from a table in Oracle, particularly for large tables where deleting rows individually may be time-consuming. However, it's essential to use TRUNCATE with caution, considering its irreversible nature and potential impact on database integrity and concurrency.
Let's demonstrate how to use the TRUNCATE statement in Oracle with an example table and some sample data.
Consider a simple table named employees with the following structure and data:-
Table Structure:-
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
department VARCHAR2(50)
);
Sample Data:-
INSERT INTO employees VALUES (1, 'John', 'Doe', 'HR');
INSERT INTO employees VALUES (2, 'Jane', 'Smith', 'Finance');
INSERT INTO employees VALUES (3, 'Michael', 'Johnson', 'IT');
INSERT INTO employees VALUES (4, 'Emily', 'Davis', 'Marketing');
Now, let's use the TRUNCATE statement to remove all rows from the employees table:-
TRUNCATE TABLE employees;
After executing the TRUNCATE statement, all rows from the employees table are removed, and the table structure remains intact. The primary key constraint is preserved, and the identity column sequence, if any, is reset to its initial value.
It's important to note that the TRUNCATE statement is a DDL (Data Definition Language) operation and cannot be rolled back. Once the TRUNCATE operation is executed, the data is permanently deleted from the table.
In this example, the employees table will be empty after truncation, as all rows have been removed:-
SELECT * FROM employees;
Output:
No rows selected
This demonstrates how to use the TRUNCATE statement in Oracle to quickly remove all rows from a table, preserving the table structure and constraints.
Here are five frequently asked questions (FAQs) about the `TRUNCATE` statement in Oracle:-
1. What is the difference between TRUNCATE and DELETE in Oracle?
- The TRUNCATE statement removes all rows from a table quickly and efficiently, deallocating storage space and resetting any associated identity columns. It is a DDL (Data Definition Language) operation and cannot be rolled back. In contrast, the `DELETE` statement removes rows one by one, generating undo and redo logs, and can be rolled back using a ROLLBACK statement.
2. Can TRUNCATE be used with tables containing foreign key constraints?
- Yes, TRUNCATE can be used with tables containing foreign key constraints. However, it does not fire any triggers or check referential integrity constraints. Therefore, you must ensure that truncating a table does not violate any integrity constraints in your database schema.
3. Does TRUNCATE release space immediately back to the tablespace?
- Yes, TRUNCATE releases space immediately back to the tablespace. It deallocates storage space used by the table and its associated objects, such as indexes and triggers, without generating undo and redo logs. This can help reclaim disk space and improve performance for large tables.
4. Can TRUNCATE be used on partitioned tables?
- Yes, TRUNCATE can be used on partitioned tables. When you truncate a partitioned table, only the data in the specified partition(s) is removed, and the table structure remains intact. This can be useful for quickly removing data from specific partitions without affecting other partitions or the overall table structure.
5. What are the considerations when using TRUNCATE on a table?
- When using TRUNCATE on a table, it's essential to consider the irreversible nature of the operation, as TRUNCATE cannot be rolled back. Additionally, ensure that truncating a table does not violate any integrity constraints, and be aware that it acquires exclusive table-level locks, preventing concurrent DML operations on the table until the operation completes.
