Summary: in this tutorial, you will learn how to use the Oracle
DROP TABLE statement to remove an existing table.
Introduction to Oracle
DROP TABLE statement
To move a table to the recycle bin or remove it entirely from the database, you use the
DROP TABLE statement:
DROP TABLE schema_name.table_name [CASCADE CONSTRAINTS | PURGE];
In this statement:
- First, indicate the table and its schema that you want to drop after the
DROP TABLEclause. If you don’t specify the schema name explicitly, the statement assumes that you are removing the table from your own schema.
- Second, specify
CASCADE CONSTRAINTSclause to remove all referential integrity constraints which refer to primary and unique keys in the table. In case such referential integrity constraints exist and you don’t use this clause, Oracle returns an error and stops removing the table.
- Third, specify
PURGEclause if you want to drop the table and release the space associated with it at once. By using the
PURGEclause, Oracle will not place the table and its dependent objects into the recycle bin.
Notice that the
PURGE clause does not allow you to roll back or recover the table that you dropped. Therefore, it is useful if you don’t want the sensitive data to appear in the recycle bin.
DROP TABLE examples
Let’s look at some examples of using the
DROP TABLE statement.
DROP TABLE example
CREATE TABLE statement creates
persons table for the demonstration:
CREATE TABLE persons ( person_id NUMBER, first_name VARCHAR2(50) NOT NULL, last_name VARCHAR2(50) NOT NULL, PRIMARY KEY(person_id) );
The following example drops the
persons table from the database:
DROP TABLE persons;
DROP TABLE CASCADE CONSTRAINTS example
The following statements create two new tables named
CREATE TABLE brands( brand_id NUMBER PRIMARY KEY, brand_name varchar2(50) ); CREATE TABLE cars( car_id NUMBER PRIMARY KEY, make VARCHAR(50) NOT NULL, model VARCHAR(50) NOT NULL, year NUMBER NOT NULL, plate_number VARCHAR(25), brand_id NUMBER NOT NULL, CONSTRAINT fk_brand FOREIGN KEY (brand_id) REFERENCES brands(brand_id) ON DELETE CASCADE );
In these tables, each brand has 1 or more cars while each car belongs to only one brand.
The following statement tries to drop the
DROP TABLE brands;
Oracle issued the following error:
ORA-02449: unique/primary keys in table referenced by foreign keys
This is because the primary key of the
brands table is currently referenced by the
brand_id column in the
The following statement returns all foreign key constraints of the
SELECT a.table_name, a.column_name, a.constraint_name, c.owner, c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk FROM all_cons_columns a JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name JOIN all_constraints c_pk ON c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_name WHERE c.constraint_type = 'R' AND a.table_name = 'CARS';
To drop the
brands table, you must use the
CASCADE CONSTRAINTS clause as follows:
DROP TABLE brands CASCADE CONSTRAINTS;
This statement dropped not only the
brands table but also the foreign key constraint
fk_brand from the
If you execute again the statement to get the foreign key constraints in the
cars table, you will not see any row returned.
DROP TABLE PURGE example
The following statement drops the
cars table using the
DROP TABLE cars purge;
Drop multiple tables at once
Oracle provides no direct way to drop multiple tables at once. However, you can use the following PL/SQL block to do it:
BEGIN FOR rec IN ( SELECT table_name FROM all_tables WHERE table_name LIKE 'TEST_%' ) LOOP EXECUTE immediate 'DROP TABLE '||rec.table_name || ' CASCADE CONSTRAINTS'; END LOOP; END; /
This block deletes all tables whose names start with
To test this code, you can first create three tables:
test_3 as follows:
CREATE TABLE test_1(c1 VARCHAR2(50)); CREATE TABLE test_2(c1 VARCHAR2(50)); CREATE TABLE test_3(c1 VARCHAR2(50));
Then, execute the PL/SQL block above.
In this tutorial, you have learned how to use the Oracle
DROP TABLE statement to drop a table from the database.