How Can I Delete a Table in Oracle SQL?
To delete a table in Oracle SQL (or any SQL for that matter), you run a statement called DROP TABLE.
Is There a DELETE Table SQL Statement?
No, there isn’t. The DELETE statement deletes rows from a table, and is specified as DELETE FROM TABLE.
How Can I Run The DROP TABLE Statement?
The syntax for the DROP TABLE command is pretty simple:
DROP TABLE tablename [CASCADE CONSTRAINTS] [PURGE];
First, you write DROP TABLE, and then the name of the table you want to drop.
The CASCADE CONSTRAINTS keyword means that all referential integrity constraints with the table will be dropped as well. If there are constraints on the table and you don’t specify this keyword, then an error will be displayed and the table will not be dropped.
If you specify PURGE, it means that the table and its dependent objects will be purged from the recycle bin, and you will no longer be able to recover the table. By default, this is not specified, and the table and objects are moved to the recycle bin and can be recovered if you need to.
What Happens When You Drop a Table in Oracle?
There are a few things that happen in Oracle when you drop a table:
- The data is removed and is no longer accessible.
- The table is no longer in the data dictionary.
- All indexes and triggers on the table are dropped.
- All views and PL/SQL program units that depend on this table will remain, but they become invalid.
- All synonyms that point to this table remain, but when you try to use them, you’ll get an error.
- All extents allocated for the table are returned to the free space of the tablespace, and can be used by other objects if required.
How Can I Undo a DROP TABLE?
You can undo the deleting of a table in Oracle SQL by using the FLASHBACK command.
The command looks like this.
FLASHBACK TABLE tablename TO BEFORE DROP;
I’ll write more about the FLASHBACK command in another post.
Examples of SQL DROP TABLE
Here are some examples of dropping deleting a table in Oracle SQL.
Example 1 – Simple drop
This statement drops a table which has no constraints.
DROP TABLE table1; Table TABLE1 dropped.
Example 2 – Constraints
This example drops a table that has constraints.
DROP TABLE table2; 02449. 00000 - "unique/primary keys in table referenced by foreign keys" *Cause: An attempt was made to drop a table with unique or primary keys referenced by foreign keys in another table. *Action: Before performing the above operations the table, drop the foreign key constraints in other tables. You can see what constraints are referencing a table by issuing the following command:
Let’s try it again with CASCADE CONSTRAINTS.
DROP TABLE table2 CASCADE CONSTRAINTS; Table TABLE1 dropped.
Now, the table has been dropped.
Example 3 – Undo DROP TABLE
Let’s drop the table and then undo it.
SELECT COUNT(*) FROM table3;
This shows us that we have a table and what’s inside it.
DROP TABLE table3; Table TABLE3 dropped.
We have just dropped the table. Let’s try select from it.
SELECT COUNT(*) FROM table3; ORA-00942: table or view does not exist
It shows an error because the table has been dropped.
Now, let’s restore it.
FLASHBACK TABLE table3 TO BEFORE DROP;
SELECT COUNT(*) FROM table3;
We can see that the table has been restored and the data is now viewable.
Example 4 – Purge
Let’s delete a table using PURGE and try to restore it.
DROP TABLE table4 PURGE; FLASHBACK TABLE table4 TO BEFORE DROP; SQL Error: ORA-38305: object not in RECYCLE BIN 38305. 00000 - "object not in RECYCLE BIN" *Cause: Trying to Flashback Drop an object which is not in RecycleBin. *Action: Only the objects in RecycleBin can be Flashback Dropped.
You can see that the FLASHBACK statement gives an error. This is because we specified the PURGE parameter when we deleted the table, so we cannot recover it using FLASHBACK.
So, that’s how you can delete tables in SQL. You use the DROP TABLE command to delete a table.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!