DROP TABLE

The DROP TABLE statement removes the specified table, including any hash indexes and any range indexes associated with it.

Required Privilege

No privilege is required for the table owner.

DROP ANY TABLE for another user's table.

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL Syntax

DROP TABLE [Owner.]TableName

Parameters

Parameter Description

[Owner.]TableName

Identifies the table to be dropped.

Description

  • If you attempt to drop a table that is in use, an error results.

  • If DROP TABLE is or was active in an uncommitted transaction, other transactions doing DML operations that do not access that table are allowed to proceed.

  • If the table is a replicated table, you can do one of the following:

    • Use the DROP REPLICATION statement to drop the replication scheme before issuing the DROP TABLE statement.

    • If DDL_REPLICATION_LEVEL is 2 or greater, the DROP TABLE statement drops the table from the active standby pair for all databases in the replication scheme.

      If DDL_REPLICATION_LEVEL is 1, stop the replication agent and use the ALTER ACTIVE STANDBY PAIR ... EXCLUDE TABLE statement to exclude the table from the replication scheme. Then use the DROP TABLE statement to drop the table.

      See "Making DDL Changes in an Active Standby Pair" in the Oracle TimesTen In-Memory Database Replication Guide for more information.

  • A temporary table cannot be dropped by a connection if some other connection has some non-empty instance of the table.

Examples

CREATE TABLE vendorperf
   (ordernumber INTEGER,
    delivday TT_SMALLINT,
    delivmonth TT_SMALLINT,
    delivyear TT_SMALLINT,
    delivqty TT_SMALLINT,
    remarks VARCHAR2(60));
CREATE UNIQUE INDEX vendorperfindex ON vendorperf (ordernumber);

The following statement drops the table and index.

DROP TABLE vendorperf;