DROP TABLE

Purpose

Use the DROP TABLE statement to remove a table or an object table and all its data from the database.

See Also: CREATE TABLE for information on creating tables ALTER TABLE for information on modifying tables

Prerequisites

The table must be in your own schema or you must have the DROP ANY TABLE system privilege.

Syntax

Keywords and Parameters

schema

Specify the schema containing the table. If you omit schema, Oracle assumes the table is in your own schema.

table

Specify the name of the table, object table, or index-organized table to be dropped. Oracle automatically performs the following operations:

• Removes all rows from the table (as if the rows were deleted).

• Drops all the table's indexes and domain indexes, regardless of who created them or whose schema contains them.

• If you drop a range-partitioned or hash-partitioned table, all the table partitions are also dropped. If you drop a composite-partitioned table, all the partitions and subpartitions are also dropped.

• For a domain index, this statement invokes the appropriate drop routines.

  See Also: Oracle8i Data Cartridge Developer's Guide for more information on these routines

• If any statistic types are associated with the table, Oracle disassociates the statistics types with the FORCE clause and removes any user-defined statistics collected with the statistics type.

  See Also: ASSOCIATE STATISTICS and DISASSOCIATE STATISTICS for more information on statistics type associations

• If the table is not part of a cluster, Oracle returns all data blocks allocated to the table and its indexes to the tablespaces containing the table and its indexes.

  Note: To drop a cluster and all its the tables, use the DROP CLUSTER statement with the INCLUDING TABLES clause to avoid dropping each table individually. See DROP CLUSTER.

• If the table is a base table for a view, a container or master table of a materialized view, or if it is referenced in a stored procedure, function, or package, Oracle invalidates these dependent objects but does not drop them. You cannot use these objects unless you re-create the table or drop and re-create the objects so that they no longer depend on the table.

• If you choose to re-create the table, it must contain all the columns selected by the queries originally used to define the materialized views/snapshots and all the columns referenced in the stored procedures, functions, or packages. Any users previously granted object privileges on the views, stored procedures, functions, or packages need not be regranted these privileges.

• If the table is a detail table for a materialized view, the materialized view can still be queried, but it cannot be refreshed unless the table is re-created so that it contains all the columns selected by the materialized view's query.

• If the table has a materialized view log, Oracle drops this log and any other direct-load INSERT refresh information associated with the table.

CASCADE CONSTRAINTS

Specify CASCADE CONSTRAINTS to drop all referential integrity constraints that refer to primary and unique keys in the dropped table. If you omit this clause, and such referential integrity constraints exist, Oracle returns an error and does not drop the table.

Example

DROP TABLE Example

The following statement drops the test_data table:

DROP TABLE test_data;