Dropping tables

For example:

• All column names are dropped.
• Indexes created on any columns of the table are dropped.
• All views based on the table are marked inoperative.
• All privileges on the dropped table and dependent views are implicitly revoked.
• All referential constraints in which the table is a parent or dependent are dropped.
• All packages and cached dynamic SQL and XQuery statements dependent on the dropped table are marked invalid, and remain so until the dependent objects are re-created. This includes packages dependent on any supertable above the subtable in the hierarchy that is being dropped.
• Any reference columns for which the dropped table is defined as the scope of the reference become "unscoped".
• An alias definition on the table is not affected, because an alias can be undefined
• All triggers dependent on the dropped table are marked inoperative.

To drop a table using the command line, enter:

   DROP TABLE <table_name>

The following statement drops the table called DEPARTMENT:

   DROP TABLE DEPARTMENT

An individual table cannot be dropped if it has a subtable. However, all the tables in a table hierarchy can be dropped by a single DROP TABLE HIERARCHY statement, as in the following example:

   DROP TABLE HIERARCHY person

There are differences when dropping a table hierarchy compared to dropping a specific table:

• DROP TABLE HIERARCHY does not activate deletion-triggers that would be activated by individual DROP table statements. For example, dropping an individual subtable would activate deletion-triggers on its supertables.
• DROP TABLE HIERARCHY does not make log entries for the individual rows of the dropped tables. Instead, the dropping of the hierarchy is logged as a single event.