Oracle TRUNCATE TABLE with Examples

TRUNCATE TABLE Statement in Oracle is used for deleting all data from table more efficiently and quickly while keeping the table structure intact.

Syntax:

The syntax for using Oracle TRUNCATE statement is as follows:

TRUNCATE TABLE schema_name.table_name
[CASCADE]
[[ PRESERVE | PURGE] MATERIALIZED VIEW LOG ]]
[[ DROP | REUSE]] STORAGE ]

Here,

• schema_name – It is the name of schema from which the table belongs. A user can delete a table only within its own schema or must have DROP ANY TABLE system privilege to delete table from other schemas.
• table_name – It is the name of table that we want to truncate. Generally, we just use the following query to delete all the rows from the table if the table belongs in our own schema.

TRUNCATE TABLE table_name;

A table may also be linked with other tables with the help of foreign key constraints, we need to use CASCADE Clause in that case.

TRUNCATE TABLE table_name
CASCADE;

The TRUNCATE TABLE CASCADE Statement is used for deleting all rows from table and also truncating all associated tables. Its important to have foreign key constraints mentioned with ON DELETE CASCADE in associated tables for TRUNCATE TABLE CASCADE to work.

MATERIALIZED VIEW LOG – This clause is used for determining whether materialized view log associated with the table should be preserved or purged at the time of truncation. By default, Oracle preserves the material view log.

STORAGE – This clause is used for specifying whether we want to drop or reuse the storage that was freed by the truncated tables. By default, storage is dropped by oracle.

Note:

While using the DELETE Statement we use WHERE clause for specifying which rows to delete. For a table with small number of rows, DELETE statement works fine, but using TRUNCATE statement is a more efficient way of deleting data, especially if the table contains huge number of rows.

Oracle TRUNCATE TABLE examples

Let’s look at some examples of using the TRUNCATE TABLE statement.

1. Basic Oracle TRUNCATE TABLE example

Let’s create a sample table, Locations2 for these examples by copying data from one of our existing tables in HR schema,i.e, Locations.

CREATE TABLE locations2 
AS
SELECT
  *
FROM
  locations; 

Now, if we want to delete all rows from this new table named, Locations2 we just use the following query:

TRUNCATE TABLE locations2;

2. Oracle TRUNCATE TABLE CASCADE example

Let’s create two more sample tables for these examples named: Teachers and Students

CREATE TABLE teachers(
    teacher_id NUMBER PRIMARY KEY,
    teacher_name VARCHAR2(100)
    subject_name VARCHAR2(100)
);

CREATE TABLE students(
    student_id NUMBER PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    joining_date DATE NOT NULL,
    subject_name VARCHAR(30) NOT NULL,
    teacher_id NUMBER NOT NULL,

    CONSTRAINT fk_teacher FOREIGN KEY (teacher_id) 
    REFERENCES teachers(teacher_id) ON DELETE CASCADE
);

Next, insert some rows into these two tables:

INSERT INTO TEACHERS 
VALUES ( 1, 'Julie', 'Chemistry');
INSERT INTO TEACHERS 
VALUES ( 2, 'Ash', 'Science');
INSERT INTO TEACHERS 
VALUES ( 3, 'Melissa', 'English');

INSERT INTO students 
VALUES ( 1, 'Donald','Trump', DATE '2020-06-14','English', 3);
INSERT INTO students 
VALUES ( 2, 'Kim','Jong', DATE '2020-06-18','Chemistry', 1);
INSERT INTO students 
VALUES ( 3, 'Barack','Obama', DATE '2020-06-22','Science', 2);

Let’s try to truncate the Students table;

TRUNCATE TABLE teachers;

Oracle returns the following error:

We need to add CASCADE clause for truncating a table with referential integrity constraints,

TRUNCATE TABLE teachers CASCADE;

This statement deletes data from teachers table and also deletes associated data from students table.

Let’s run a query to see if it worked:

SELECT
  *
FROM
  teachers;

SELECT
  *
FROM
  students;

It is important to specify ON DELETE CASCADE for the foreign key constraint that we created in students table, without which this TRUNCATE TABLE CASCADE would fail.

In this article, we learned about the usage of TRUNCATE TABLE statement for truncating data from tables.