DROP TYPE

Purpose

Use the DROP TYPE statement to drop the specification and body of an object, a varray, or nested table type.

See Also: DROP TYPE BODY for information on dropping just the body of an object type CREATE TYPE for information on creating types.

Prerequisites

The object, varray, or nested table type must be in your own schema or you must have the DROP ANY TYPE system privilege.

Syntax

Keywords and Parameters

schema

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

type_name

Specify the name of the object, varray, or nested table type to be dropped. You can drop only types with no type or table dependencies.

If type_name is a statistics type, this statement will fail unless you also specify FORCE. If you specify FORCE, Oracle first disassociates all objects that are associated with type_name, and then drops type_name.

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

If type_name is an object type that has been associated with a statistics type, Oracle first attempts to disassociate type_name from the statistics type and then drop type_name. However, if statistics have been collected using the statistics type, Oracle will be unable to disassociate type_name from the statistics type, and this statement will fail.

If type_name is an implementation type for an indextype, the indextype will be marked INVALID.

Unless you specify FORCE, you can drop only object, nested table, or varray types that are standalone schema objects with no dependencies. This is the default behavior.

See Also: CREATE INDEXTYPE

FORCE

Specify FORCE to drop the type even if it has dependent database objects. Oracle marks UNUSED all columns dependent on the type to be dropped, and those columns become inaccessible.

Caution: Oracle does not recommend that you specify FORCE to drop types with dependencies. This operation is not recoverable and could cause the data in the dependent tables or columns to become inaccessible. For information about type dependencies, see Oracle8i Application Developer's Guide - Fundamentals.

Example

DROP TYPE Example

The following statement removes object type person_t:

DROP TYPE person_t;