Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Basics / Oracle DROP TABLE

Oracle DROP TABLE

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];
Code language: SQL (Structured Query Language) (sql)

In this statement:

  • First, indicate the table and its schema that you want to drop after the DROP TABLE clause. 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 CONSTRAINTS clause 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 PURGE clause if you want to drop the table and release the space associated with it at once. By using the PURGE clause, 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.

Oracle DROP TABLE examples

Let’s look at some examples of using the DROP TABLE statement.

Basic Oracle DROP TABLE example

The following 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) );
Code language: SQL (Structured Query Language) (sql)

The following example drops the persons table from the database:

DROP TABLE persons;
Code language: SQL (Structured Query Language) (sql)

Oracle DROP TABLE CASCADE CONSTRAINTS example

The following statements create two new tables named brands and cars:

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 );
Code language: SQL (Structured Query Language) (sql)

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 brands table:

DROP TABLE brands;
Code language: SQL (Structured Query Language) (sql)

Oracle issued the following error:

ORA-02449: unique/primary keys in table referenced by foreign keys
Code language: SQL (Structured Query Language) (sql)

This is because the primary key of the brands table is currently referenced by the brand_id column in the cars table.

The following statement returns all foreign key constraints of the cars table:

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';
Code language: SQL (Structured Query Language) (sql)

To drop the brands table, you must use the CASCADE CONSTRAINTS clause as follows:

DROP TABLE brands CASCADE CONSTRAINTS;
Code language: SQL (Structured Query Language) (sql)

This statement dropped not only the brands table but also the foreign key constraint fk_brand from the cars table.

If you execute again the statement to get the foreign key constraints in the cars table, you will not see any row returned.

Oracle DROP TABLE PURGE example

The following statement drops the cars table using the PURGE clause:

DROP TABLE cars purge;
Code language: SQL (Structured Query Language) (sql)

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; /
Code language: SQL (Structured Query Language) (sql)

This block deletes all tables whose names start with TEST_.

To test this code, you can first create three tables: test_1, test_2 and 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));
Code language: SQL (Structured Query Language) (sql)

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.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle Drop Column
Next Oracle TRUNCATE TABLE

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.