This Oracle tutorial explains how to use the Oracle DROP TABLE statement with syntax and examples.
The Oracle DROP TABLE statement allows you to remove or delete a table from the Oracle database.
The syntax for the Oracle DROP TABLE statement is:
DROP TABLE [schema_name].table_name
[ CASCADE CONSTRAINTS ]
[ PURGE ];
Parameters or Arguments
The name of the schema that owns the table.
The name of the table to remove from the Oracle database.
Optional. If specified, all referential integrity constraints will be dropped as well.
Optional. If specified, the table and its dependent objects will be purged from the recycle bin and you will not be able to recover the table. If not specified, the table and its dependent objects are placed in the recycle bin and can be recovered later, if needed.
- If there are referential integrity constraints on table_name and you do not specify the CASCADE CONSTRAINTS option, the DROP TABLE statement will return an error and Oracle will not drop the table.
Let's look at an example that shows how to drop a table in Oracle by using the DROP TABLE statement.
DROP TABLE customers;
This Oracle DROP TABLE example would drop the table called customers.
Let's look at how to use the PURGE option with the DROP TABLE statement in Oracle.
When issuing a DROP TABLE statement in Oracle, you can specify the PURGE option. The PURGE option will purge the table and its dependent objects so that they do not appear in the recycle bin. The risk of specifying the PURGE option is that you will not be able to recover the table. However, the benefit of using PURGE is that you can ensure that sensitive data will not be left sitting in the recycle bin.
DROP TABLE customers PURGE;
This DROP TABLE statement would drop the table called customers and issue a PURGE so that the space associated with the customers table is released. In other words, the customers table is not placed into the recycle bin and, therefore, can not be recovered later if required.