Oracle TRUNCATE TABLE

Summary: in this tutorial, you will learn how to use the Oracle TRUNCATE TABLE statement to delete all data from a table faster and more efficiently.

Introduction to Oracle TRUNCATE TABLE statement

When you want to delete all data from a table, you use the DELETE statement without theWHERE clause as follows:

DELETE FROM table_name;Code language: SQL (Structured Query Language) (sql)

For a table with a small number of rows, the DELETE statement does a good job. However, when you have a table with a large number of rows, using the DELETE statement to remove all data is not efficient.

Oracle introduced the TRUNCATE TABLE statement that allows you to delete all rows from a big table.

The following illustrates the syntax of the Oracle TRUNCATE TABLE statement:

TRUNCATE TABLE schema_name.table_name
[CASCADE]
[[ PRESERVE | PURGE] MATERIALIZED VIEW LOG ]]
[[ DROP | REUSE]] STORAGE ]
Code language: SQL (Structured Query Language) (sql)

By default, to remove all rows from a table, you specify the name of the table that you want to truncate in the TRUNCATE TABLE clause:

TRUNCATE TABLE table_name;
Code language: SQL (Structured Query Language) (sql)

In this case, because we don’t specify the schema name explicitly, Oracle assumes that we truncate the table from our own schema.

If a table has relationships with other tables via the foreign key constraints, you need to use the CASCADE clause:

TRUNCATE TABLE table_name
CASCADE;
Code language: SQL (Structured Query Language) (sql)

In this case, the TRUNCATE TABLE CASCADE statement deletes all rows from the table_name, and recursively truncates down the associated tables in the chain.

Note that the TRUNCATE TABLE CASCADE statement requires the foreign key constraints defined with the ON DELETE CASCADE clause to work.

The MATERIALIZED VIEW LOG clause allows you to specify whether a materialized view log defined on the table is to be preserved or purged when the table is truncated. By default, the material view log is preserved.

The STORAGE clause allows you to choose either drop or reuse storage freed by the truncated rows and associated indexes if any. By default, the storage is dropped.

Note that to truncate a table, it must be in your own schema or you must have the DROP ANY TABLE system privilege.

Oracle TRUNCATE TABLE examples

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

A) Oracle TRUNCATE TABLE simple example

The following statement creates a table named customers_copy and copies data from the customers table in the sample database:

CREATE TABLE customers_copy 
AS
SELECT
  *
FROM
  customers; 
Code language: SQL (Structured Query Language) (sql)

To delete all rows from the customers_copy table you use the following TRUNCATE TABLE statement:

TRUNCATE TABLE customers_copy;Code language: SQL (Structured Query Language) (sql)

B) Oracle TRUNCATE TABLE CASCADE example

First, let’s create quotations and quotation_items tables for the demonstration:

CREATE TABLE quotations (
    quotation_no NUMERIC GENERATED BY DEFAULT AS IDENTITY,
    customer_id NUMERIC NOT NULL,
    valid_from DATE NOT NULL,
    valid_to DATE NOT NULL,
    PRIMARY KEY(quotation_no)
);

CREATE TABLE quotation_items (
    quotation_no NUMERIC,
    item_no NUMERIC ,
    product_id NUMERIC NOT NULL,
    qty NUMERIC NOT NULL,
    price NUMERIC(9 , 2 ) NOT NULL,
    PRIMARY KEY (quotation_no , item_no),
    CONSTRAINT fk_quotation FOREIGN KEY (quotation_no)
        REFERENCES quotations
        ON DELETE CASCADE
);
Code language: SQL (Structured Query Language) (sql)

Next, insert some rows into these two tables:

INSERT INTO quotations(customer_id, valid_from, valid_to)
VALUES(100, DATE '2017-09-01', DATE '2017-12-01');

INSERT INTO quotation_items(quotation_no, item_no, product_id, qty, price)
VALUES(1,1,1001,10,90.5);

INSERT INTO quotation_items(quotation_no, item_no, product_id, qty, price)
VALUES(1,2,1002,20,200.5);

INSERT INTO quotation_items(quotation_no, item_no, product_id, qty, price)
VALUES(1,3,1003,30, 150.5);
Code language: SQL (Structured Query Language) (sql)

Then, truncate the quotation table:

TRUNCATE TABLE quotations;
Code language: SQL (Structured Query Language) (sql)

The statement failed and Oracle returned the following error:

SQL Error: ORA-02266: unique/primary keys in table referenced by enabled foreign keys
Code language: SQL (Structured Query Language) (sql)

To fix this, you add the CASCADE clause to the TRUNCATE TABLE statement above:

TRUNCATE TABLE quotations CASCADE;
Code language: SQL (Structured Query Language) (sql)

This statement deleted data from not only quotations table but also quotation_items table.

Finally, verify whether the data from both quotations and quotation_items are deleted or not:

SELECT
  *
FROM
  quotations;

SELECT
  *
FROM
  quotation_items;
Code language: SQL (Structured Query Language) (sql)

Notice that if we did not specify the ON DELETE CASCADE for the fk_quotation constraint, the TRUNCATE TABLE CASCADE statement above would fail.

In this tutorial, you have learned how to use Oracle TRUNCATE TABLE statement to delete all data from a table faster and more efficiently.

Was this tutorial helpful?