DROP TABLE
To move a table to recycle bin or to completely delete a table from the Oracle database, Oracle DROP TABLE statement is used.
Syntax:
DROP TABLE schema_name.table_name [CASCADE CONSTRAINTS | PURGE];
Parameters:
schema_name: It is used to specify the name of the schema that owns the table to be removed. It is necessary to mention the schema name explicitly, otherwise, the DROP TABLE statement assumes to delete the table from the users’ own schema.
table_name: It is used to specify the name of the table to be eliminated from the Oracle database.
CASCADE CONSTRAINTS: In case referential integrity constraints exist for the table than this parameter is used to remove all the referential integrity constraints along with the table, which otherwise would return an error and the table removal process will stop, if this parameter is not specified.
PURGE: It is used to remove the table from the database and release the space associated with it in the recycle bin at once and thus can’t be recovered. It is thus useful to remove any sensitive data permanently from the system.
Example 1: Remove a table.
DROP TABLE students; |
Explanation:
The “students” is an already existing table which will be removed from the database and will be placed in the recycle bin.
Example 2: Drop a table with PURGE parameter.
DROP TABLE students PURGE |
Explanation:
The “students” is an already existing table which will be removed from the database but will not be placed in the recycle bin. The PURGE parameter will release the space associated with the table in the recycle bin and thus the table will be permanently deleted from the database.
DROP Multiple Tables:
To Drop multiple tables at once, there is no direct way hat Oracle facilitates. However, a User can create a PL/SQL block to Drop multiple tables at once.