This tutorial aims to show you how to work with the DROP TABLE command in Oracle databases.
This command removes an existing table from a given database either to the recycle bin or permanently.
Oracle Drop Table Statement
The following defines the syntax of the DROP TABLE command:
[CASCADE CONSTRAINTS | PURGE];
We start by calling the DROP TABLE command followed by the schema name in which the target table resides. Unlike other database engines, Oracle requires you to specify the target schema.
Otherwise, Oracle will assume that you wish to remove the table from your schema, which might not be your intended operation.
Next is the table name that you wish to remove. Again, this must be an existing table in the specified schema. Finally, you must also have sufficient privileges on the target table to execute a drop operation.
The second part, which is the CASCADE CONSTRAINTS, tells Oracle to remove all referential integrity constraints associated with the table’s primary and unique keys.
If you do not specify this clause and the table contains such referential constraints, Oracle returns an error and fails to remove the table.
The PURGE keyword tells Oracle to remove the table from the schema and release the allocated space. The PURGE option allows you to permanently remove the table instead of placing it in the recycle bin.
CAUTION: Be careful when using the PURGE option. This is because it completely removes the ability to perform a rollover or table recovery after executing the drop command. Skip this option if you wish to recover the table for later use.
Similarly, it can be a helpful feature when you need to shred some sensitive databases from the recycle bin.
Oracle Drop Table Example Illustration
The following are examples of using the DROP TABLE statement in Oracle databases.
Example 1: Basic Drop Table Command
The following illustration shows the simple create table statements that initialize a simple table for illustration purposes.
Feel free to skip this section if you have a target table that you wish to remove. Otherwise, let us proceed.
film_id NUMBER
GENERATED AS IDENTITY,
title VARCHAR(100) NOT NULL,
release_year INT NOT NULL,
description VARCHAR(255),
language VARCHAR(50),
rating VARCHAR(50),
imbd_score FLOAT,
rental_rate FLOAT
);
Once we have the provided sample table, we can drop it as shown in the following query:
In this case, since the table does not contain any constraints, we do not need to use the CASCADE CONSTRAINTS clause.
Example 2: Drop Table with Constraints
In this example, we wish to illustrate how to remove a table with primary and foreign key constraints.
studio_id NUMBER PRIMARY KEY,
studio_name VARCHAR2(50)
);
CREATE TABLE film (
film_id number primary key,
title VARCHAR(100) NOT NULL,
release_year INT NOT NULL,
description VARCHAR(255),
language VARCHAR(50),
rating VARCHAR(50),
imbd_score FLOAT,
rental_rate FLOAT,
studio_id number not null,
CONSTRAINT f_studio FOREIGN KEY(studio_id)
REFERENCES studio (studio_id)
ON DELETE CASCADE
);
If we attempt to drop the table without removing the constraints, oracle returns an error as shown:
Output:
This is because the ID references the primary key of the studio table.
Therefore, to remove the studio table, we need to use the CASCADE CONSTRAINTS keyword as shown in the following:
With that, you should successfully remove the table from the schema.
It is good to ensure that the referenced objects are not critical. In some cases, cascading the constraints can lead to data failure on the tables and the entire database. Use it carefully.
Example 3: Delete Table (Purge Mode)
To remove a table without storing it in the recycle bin, we can use the PURGE keyword as illustrated in the following illustration:
This should allow you to permanently remove the table and release the occupied space back to the disk.
Example 4: Delete Multiple Tables
Unlike other well-thought-out databases such as PostgreSQL, MySQL, and SQL Server, Oracle does not provide a way of removing multiple tables at once.
You can, however, use an SQL block to perform such operation as shown in the following:
for i in
(
select tbl_name
from all_tables
where table_name like 'pattern'
)
loop
execute immediate 'drop table'||i.tbl_name || 'cascade constraints' || 'purge';
end loop;
end;
You can modify the previous script to remove the tables following the specified pattern.
Conclusion
In this post, we explored the fundamentals of working with the drop table command in Oracle. This command allows you to remove an existing table from a given schema. You also learned how to delete a table to recycle bin or remove it permanently.
