Database Tutorial

You use the DROP TABLE statement to remove tables from the database. The DROP TABLE statement can fail when other tables or views have referential integrity (foreign key) dependencies on the table or view.

Oracle lets you drop only a single table with a DROP TABLE statement. You can drop tables when they contain data or when they’re empty. This statement also drops global temporary tables. You can set aside referential integrity by using the CASCADE CONSTRAINTS clause.

The prototype for the DROP TABLE statement is shown here:

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

A DROP TABLE statement against a table that has foreign keys referencing it raises an exception:

DROP TABLE parent
          *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

The CASCADE CONSTRAINTS clause removes dependencies from other tables, such as foreign key constraints that reference the table. It does not remove the data from the other table’s previous foreign key column, which is important if you plan on re-creating the table and re- importing data. Any re-import of data would need to ensure that primary key values would map to existing foreign key values.

The PURGE keyword is optional for tables but is required when you’re dropping a partitioned table. The PURGE keyword starts a series of subtransactions that drop all the partitions of the table. The first successful subtransaction marks the table as UNUSABLE in the data catalog. This flag in the data catalog ensures that only a DROP TABLE statement works against the remnants of the table. If you encounter a problem trying to access a table, you can query the status column value to see if it’s unusable. The UNUSABLE column is in the CDB_, DBA_, ALL_, and USER_TABLES, administrative views and in the USER_PART_TABLES, USER_ALL_TABLES, USER_OBJECT_TABLES, and USER_OBJECTS administrative views.

Indexes are sometimes dropped because they no longer apply or because the cost of dropping and re-creating them is less than the cost of altering it. You drop an index with the following syntax:

DROP INDEX index_name;

After dropping an obsolete index, it’s always a good idea to verify the impact of removing an index by testing SQL statements that previously used the index.

Dropping a view works much like dropping a table. The syntax is simple:

DROP VIEW view_name;

After dropping an obsolete index, it’s always a good idea to verify the impact of removing an index by testing SQL statements that previously used the index.

Dropping an object type has two versions: one where the type has no dependents and another where the type has dependents. Oracle database has simplified dropping types by adding the FORCE keyword to the DROP TYPE statement.

You would drop a type without any type dependents with the following syntax:

DROP TYPE type_name;

You would drop a type with type dependents with the FORCE keyword, which in turn removes dependent types and columns from tables that use that type or any of its dependents. Therefore, you should be absolutely sure you understand the consequences before you attempt this command with the FORCE option.

DROP TYPE type_name FORCE;