A Guide to the Oracle TRUNCATE TABLE StatementThe Oracle TRUNCATE TABLE statement is a useful statement for removing data in Oracle SQL. I’ll explain what the statement does and show you some examples in this article.

What Does the Oracle TRUNCATE TABLE Statement Do?

The Oracle TRUNCATE statement, or TRUNCATE TABLE statement, removes all data from a table.

It’s similar to the DELETE statement without a WHERE clause.

 

TRUNCATE Syntax

The syntax for the Oracle TRUNCATE statement is:

TRUNCATE TABLE [schema_name.]table_name

[PRESERVE MATERIALIZED VIEW LOG | PURGE MATERIALIZED VIEW LOG]

[DROP STORAGE | REUSE STORAGE]

So what do all of these parameters mean?

  • schema_name: This is optional. If your table belongs to a different schema, you specify it here.
  • table_name: The name of the table to remove the data from.
  • PRESERVE MATERIALIZED VIEW LOG: Optional, and default. If you specify this, then the materialized view log will be preserved when you truncate the table.
  • PURGE MATERIALIZED VIEW LOG: Optional. If you specify this, then the materialized view log will be purged when you truncate the table.
  • DROP STORAGE: Optional, and default. If you specify this, all storage that is allocated to the truncated rows will be deallocated (except the space allocated by MINEXTENTS).
  • REUSE STORAGE: If you specify this, all storage that is allocated to the truncated rows will remain allocated to the table.

 

What’s the Difference Between TRUNCATE and DELETE?

So what’s the difference between the TRUNCATE and DELETE statements?

DELETE can delete specific records, TRUNCATE deletes all records

You may have noticed that there is no WHERE clause on the TRUNCATE statement. This is because all of the data in the table is removed when you run a TRUNCATE statement.

With the DELETE statement, you can delete all records, or use the WHERE clause to delete some records.

DELETE Allows You To Rollback, TRUNCATE Does Not Allow Rollback

With the DELETE statement, you can run a ROLLBACK statement and effectively restore the data you have deleted. However, if you run the ROLLBACK statement after a TRUNCATE statement, then you won’t be able to recover the data. You can’t rollback a TRUNCATE statement.

DELETE generates an undo log, TRUNCATE does not

This is what makes TRUNCATE statements faster than DELETE statements. When you run a DELETE statement, the undo log is generated so that the ROLLBACK statement can be run.

DELETE takes locks on rows, TRUNCATE does not

When you run a DELETE statement, the rows are locked while they are deleted. With the TRUNCATE statement, they are not locked.

DELETE causes delete triggers to fire, TRUNCATE does not

If you use triggers, this might cause different behaviour in your database.

DELETE is a DML command, TRUNCATE is a DDL command.

This difference means that DELETE can be rolled back and TRUNCATE cannot.

 

Oracle TRUNCATE TABLE Examples

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

Example 1 – Basic Truncate

This is a basic TRUNCATE statement.

TRUNCATE TABLE student;

Example 2 – Materalized View Log

This example uses the PRESERVE MATERIALIZED VIEW LOG keyword.

TRUNCATE TABLE student
PRESERVE MATERIALIZED VIEW LOG;

Example 3 – Storage

This example uses the REUSE STORAGE command.

TRUNCATE TABLE student
REUSE STORAGE;

Example 4 – All Parameters

This example uses both the “materialized view log” parameter and the “storage” parameter.

TRUNCATE TABLE student
PRESERVE MATERIALIZED VIEW LOG
DROP STORAGE; 

As you can see, the TRUNCATE TABLE statement is pretty simple to use. In most cases you can just run the “TRUNCATE TABLE tablename” command.

 

Truncate Table in a Stored Procedure

Are you trying to run a TRUNCATE TABLE statement inside a stored procedure? And are you getting an error?

Your procedure may look like this:

CREATE PROCEDURE testProc IS
BEGIN
  TRUNCATE TABLE tablename;
END testProc;

If you run this, you might get an error.

This error happens because the stored procedure is running the DDL statement, and some instances of the DDL could invalidate the stored procedure.

To resolve it, you could run the command as Execute Immediate.

Change your stored procedure to call the TRUNCATE statement like this:

CREATE PROCEDURE testProc IS
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE tablename';
END testProc;

This procedure should now run successfully.

 

So, that’s how you use the TRUNCATE statement, and how it’s different to the DELETE statement.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Want To Improve Your Oracle SQL?

Improve your Oracle SQL queries by signing up to my free 10-day Oracle Tips email course.

BONUS: Oracle SQL functions PDF cheat sheet

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit