Deleting (Dropping) a Table in Oracle 23c

In Oracle 23c, deleting a table is more than just removing data. When you drop a table, you remove its structure, its indexes, and many related objects from the database. Because dropping a table is a potentially destructive operation, Oracle provides safety features such as the Recycle Bin and Flashback, as well as options to control how constraints and dependencies are handled.

1. The basic DROP TABLE statement

To remove a table and all of its rows from the database, use the DROP TABLE statement:

DROP TABLE table_name;
  

A more complete syntax, including constraint and safety options, is:

DROP TABLE [schema.]table_name
  [CASCADE CONSTRAINTS]
  [PURGE];

• schema. – Optional schema that owns the table. If omitted, Oracle uses your current schema.
• CASCADE CONSTRAINTS – Drops all referential integrity constraints in other tables that reference this table’s primary or unique keys. If you omit this clause and such constraints exist, Oracle raises an error and does not drop the table.
• PURGE – Permanently drops the table and bypasses the Recycle Bin. Use this only when you are certain you will not need to restore the table with Flashback.

You must own the table or have appropriate system privileges (for example, DROP ANY TABLE) to execute this statement.

2. What happens when you drop a table?

Dropping a table has several important effects:

1. All rows are removed. The data stored in the table is deleted.
2. The table structure is removed. The table definition (columns, datatypes, constraints) is removed from the data dictionary.
3. Dependent physical objects are dropped. Indexes, materialized view logs, and internal segment structures owned by the table are removed.
4. Data dictionary metadata is updated. Oracle removes or invalidates metadata entries related to the dropped table.

Other database objects that reference the table are not physically removed, but they are affected:

• Views referencing the table become invalid and will fail until they are redefined or the table is re-created.
• Stored PL/SQL (procedures, functions, packages) that reference the table become invalid and must be recompiled or modified.
• Foreign keys in child tables that reference the dropped table’s primary or unique key are dropped if you specify CASCADE CONSTRAINTS; otherwise, they prevent the drop.
• Synonyms that point to the table remain but become invalid.

In Oracle 23c, DROP TABLE is a DDL statement and performs an implicit commit before and after execution. You cannot roll back a successfully executed DROP TABLE with ROLLBACK; instead, you rely on the Recycle Bin and Flashback (if enabled) or traditional backups.

3. Recycle Bin and Flashback: safety net for DROP TABLE

In most non-system tablespaces, when you drop a table without PURGE, Oracle moves the table to the Recycle Bin instead of immediately releasing its storage. The table is renamed internally (for example, to a name starting with BIN$), but you can still restore it.

To restore a dropped table, use FLASHBACK TABLE:

FLASHBACK TABLE table_name TO BEFORE DROP;

This statement recovers the table (and, if possible, its dependent indexes) from the Recycle Bin. If you are certain a table should never be recovered—for example, for regulatory or privacy reasons—use PURGE when dropping it:

DROP TABLE table_name PURGE;

Alternatively, you can purge specific Recycle Bin objects later using:

PURGE TABLE table_name;
-- or
PURGE RECYCLEBIN;

4. DROP vs TRUNCATE vs DELETE

Dropping a table is different from removing its data. Oracle provides three main mechanisms, each with distinct effects and performance characteristics:

4.1 DROP TABLE

• Removes the table definition and its data.
• Drops dependent indexes and many related physical objects.
• Invalidates views and PL/SQL that reference the table.
• Implicitly commits the transaction; cannot be rolled back. Recovery is via Flashback or backup, not via ROLLBACK.

4.2 TRUNCATE TABLE

Use TRUNCATE TABLE to remove all rows but keep the table structure:

TRUNCATE TABLE table_name;

• Deletes all rows efficiently, with minimal undo and redo.
• Keeps the table definition, constraints, and indexes (though index statistics may change).
• Implicitly commits; cannot be rolled back.
• Does not move the table to the Recycle Bin.

4.3 DELETE FROM

Use DELETE when you want transactional control over row removal:

DELETE FROM table_name;

• Removes rows but preserves the table, its indexes, and constraints.
• Can be filtered with a WHERE clause.
• Generates undo and redo proportional to the number of rows modified.
• Can be committed or rolled back like any other DML statement.

In summary, choose:

• DROP TABLE when you want to remove the table itself.
• TRUNCATE TABLE when you want a fast, non-transactional way to empty a table.
• DELETE when you need transactional, row-level control.

5. Practical checklist before dropping a table

Before issuing DROP TABLE in an Oracle 23c environment, follow this checklist:

1. Confirm business requirements. Verify that the table is no longer needed or that its data has been archived elsewhere.
2. Check dependencies. Use data dictionary views such as DBA_DEPENDENCIES, ALL_CONSTRAINTS, and ALL_VIEWS to find views, PL/SQL units, and foreign keys that reference the table.
3. Decide on constraint handling. If child tables reference this table, determine whether you will use CASCADE CONSTRAINTS or adjust foreign keys manually.
4. Plan for recovery. Ensure that the Recycle Bin is enabled or that you have appropriate backups if you need the option to restore the table later.
5. Choose the correct operation. Use DROP, TRUNCATE, or DELETE based on whether you are removing the structure, only the data, or just a subset of rows.

Understanding how DROP TABLE interacts with constraints, dependencies, and the Recycle Bin is essential for safely managing schema changes in Oracle 23c. In the next lesson, you will review the module and reinforce these concepts with examples and a quiz.