Oracle7 Server SQL Reference

DROP TABLE

Purpose

To remove a table and all its data from the database.

Prerequisites

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

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the table's creation label or you must satisfy one of the following criteria:

If the table's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges

If the table's creation label is lower than your DBMS label, you must have WRITEDOWN system privilege.

If the table's creation label and your DBMS label are not comparable, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

Syntax

Keywords and Parameters

schema

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

table

is the name of the table to be dropped.

CASCADE CONSTRAINTS

drops all referential integrity constraints that refer to primary and unique keys in the dropped table. If you omit this option, and such referential integrity constraints exist, Oracle7 returns an error message and does not drop the table.

Usage Notes

When you drop a table, Oracle7 also automatically performs the following operations:

Oracle7 removes all rows from the table (as if the rows were deleted).

Oracle7 drops all the table's indexes, regardless of who created them or whose schema contains them.

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

If the table is a base table for views or if it is referenced in stored procedures, functions, or packages, Oracle7 invalidates these objects but does not drop them. You cannot use these objects unless you recreate the table or drop and recreate the objects so that they no longer depend on the table.

If you choose to recreate the table, it must contain all the columns selected by the queries originally used to define the views and all the columns referenced in the stored procedures, functions, or packages. Note that any users previously granted object privileges on the views, synonyms, stored procedures, functions, or packages need not be regranted these privileges.

If the table is a master table for snapshots, Oracle7 does not drop the snapshots. Such a snapshot can still be queried, but it cannot be refreshed unless the table is recreated so that it contains all the columns selected by the snapshot's query.

If you choose to recreate the table, it must contain all the columns selected by the queries originally used to define the snapshots.

If the table has a snapshot log, Oracle7 drops the snapshot log.

You can drop a cluster and all of its tables using the DROP CLUSTER command with the INCLUDING TABLES clause and avoid dropping each table individually.

Example

The following statement drops the TEST_DATA table:

DROP TABLE test_data