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.
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.
Get Your SQL Cheat Sheet
Three pages of SQL commands to save/print for easy reference
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. Related: SQL Views: The Complete Guide.
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!