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, 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. It completely removes the ability to perform a rollover or table recovery after executing the drop command. Therefore, skip this option if you wish to recover the table for later use. However, it can be a helpful feature when you need to shred the 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 Oracle Drop Table Command
The following 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, proceed to the proceeding step.
film_id NUMBER
GENERATEDASIDENTITY,
title VARCHAR(100) NOTNULL,
release_year INTNOTNULL,
description VARCHAR(255),
languageVARCHAR(50),
rating VARCHAR(50),
imbd_score FLOAT,
rental_rate FLOAT
);
Once we have the given 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: Oracle Table 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)
);
CREATETABLE film (
film_id NUMBER PRIMARY KEY,
title VARCHAR(100) NOTNULL,
release_year INTNOTNULL,
description VARCHAR(255),
languageVARCHAR(50),
rating VARCHAR(50),
imbd_score FLOAT,
rental_rate FLOAT,
studio_id numbernotnull,
CONSTRAINT f_studio FOREIGNKEY(studio_id)
REFERENCES studio (studio_id)
ONDELETECASCADE
);
If we attempt to drop the table without removing the constraints, Orcalereturns 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 example:
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 constraints can lead to data failure on the tables and the entire database. Use it carefully.
Example 3: Oracle 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 example:
This should allow you to permanently remove the table and release the occupied space back to the disk.
Example 4: Oracle Delete Multiple Tables
Unlike other well-thought-out databases such as PostgreSQL, MySQL, and SQL Server, Oracle does not provide a way of removing the multiple tables at once.
You can, however, use an SQL block to perform such an operation as follows:
FOR i IN
(
SELECT tbl_name
FROM all_tables
WHERE table_name LIKE'pattern'
)
LOOP
executeimmediate'drop table'||i.tbl_name || 'cascade constraints' || 'purge';
endloop;
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. We hope you found this article helpful!
