Using TRUNCATE TABLE Statement in Oracle

This article is written about how to use the Oracle TRUNCATE TABLE declaration with syntax, examples, and practice exercises.

Description

The TRUNCATE TABLE assertion is used to put off all documents from a table in Oracle. It performs the same characteristic as a DELETE declaration without a WHERE clause.

Warning: If you truncate a table, the TRUNCATE TABLE assertion can now not be rolled back.

Syntax

The syntax for the TRUNCATE TABLE declaration in Oracle/PLSQL is:

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

Parameters or Arguments

schema_name Optional. If specified, it is the name of the schema that the table belongs to. table_name The table that you desire to truncate. PRESERVE MATERIALIZED VIEW LOG Optional. If specified, the materialized view log will be preserved when the table is truncated. This is the default behavior. PURGE MATERIALIZED VIEW LOG Optional. If specified, the materialized view log will be purged when the table is truncated. DROP STORAGE Optional. If specified, all storage for the truncated rows will be deallocated, besides the space that has been allotted by MINEXTENTS. This is the default behavior. REUSE STORAGE Optional. If specified, all storage for the truncated rows will remain allotted to the table.

Example

In Oracle, truncating a table is a fast way to clear out records from a table if you don’t need to worry about rolling back. One of the reasons is that when the table is truncated, it does not have an effect on any of the table’s indexes, triggers, or dependencies. Truncating a desk is additionally a lot less difficult than shedding the table and recreating it.

Let’s appear at an instance of how to use the TRUNCATE TABLE announcement in Oracle/PLSQL.

For example:

TRUNCATE TABLE customers;

This example would truncate the table known as customers and cast off all data from that table.

It would be equivalent to the following DELETE assertion in Oracle:

DELETE FROM customers;

Both of these statements would result in all information from the customers table being deleted. The foremost difference between the two is that you can roll back the DELETE statement if you choose, however you cannot roll returned the TRUNCATE TABLE statement.

Let’s seem to be at one more instance the place we prefix the desk title with the schema name.

For example:

TRUNCATE TABLE totn.suppliers;

This instance would truncate the desk known as suppliers in the schema called totn. Before you can truncate a table in some other schema, you have to have the quintessential privileges such as DROP ANY TABLE.