7
SQL Statements (continued)

DROP DATABASE LINK

Syntax

Purpose

To remove a database link from the database.

Prerequisites

To drop a private database link, the database link must be in your own schema. To drop a PUBLIC database link, you must have the DROP PUBLIC DATABASE LINK system privilege.

For information on creating database links, see "CREATE DATABASE LINK".

Keywords and Parameters

PUBLIC

must be specified to drop a PUBLIC database link.

dblink

specifies the database link to be dropped.
Restriction: You cannot drop a database link in another user's schema and you cannot qualify dblink with the name of a schema. (Periods are permitted in names of database links. Therefore, Oracle interprets the entire name, such as RALPH.LINKTOSALES, as the name of a database link in your schema rather than as a database link named LINKTOSALES in the schema RALPH.)

Example

The following statement drops a private database link named BOSTON:

DROP DATABASE LINK boston;

DROP DIMENSION

Syntax

Purpose

To remove the named dimension.

For information on materialized views and their use of dimensions, see Oracle8i Concepts. See also "CREATE DIMENSION".

Prerequisites

The dimension must be in your own schema or you must have the DROP ANY DIMENSION system privilege to use this statement.

Keywords and Parameters

schema

is the name of the schema in which the dimension is located. If you omit schema, Oracle assumes the dimension is in your own schema.

dimension

is the name of the dimension you want to drop. The dimension must already exist.

This statement does not invalidate materialized views that use relationships specified in dimension. However, requests that have been rewritten by query rewrite may be invalidated, and subsequent operations on such views may execute more slowly.

Example

This example drops the TIME dimension:

DROP DIMENSION time;

DROP DIRECTORY

Syntax

Purpose

Use DROP DIRECTORY to remove a directory object from the database.

For information on creating a directory, see "CREATE DIRECTORY".

Prerequisites

To drop a directory you must have the DROP ANY DIRECTORY system privilege.

WARNING:
Do not drop a directory when files in the associated file system are being accessed by PL/SQL or OCI programs.

Keywords and Parameters

directory_name

is the name of the directory database object to be dropped.
Oracle removes the directory object, but does not delete the associated operating system directory on the server's file system.

Example

The following statement drops the directory object BFILE_DIR:

DROP DIRECTORY bfile_dir;

DROP FUNCTION

Syntax

Purpose

To remove a standalone stored function from the database. For information on creating a function, see "CREATE FUNCTION".

Note:
Do not use this statement to remove a function that is part of a package. Instead, either drop the entire package using the DROP PACKAGE statement or redefine the package without the function using the CREATE PACKAGE statement with the OR REPLACE clause.

Prerequisites

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

Keywords and Parameters

schema

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

function_name

is the name of the function to be dropped.
Oracle invalidates any local objects that depend on, or call, the dropped function. If you subsequently reference one of these objects, Oracle tries to recompile the object and returns an error if you have not re-created the dropped function. For more information on how Oracle maintains dependencies among schema objects, including remote objects, see .

If any statistics types are associated with the function, Oracle disassociates the statistics types with the FORCE option and drops any user-defined statistics collected with the statistics type. For more information on statistics type associations, see "ASSOCIATE STATISTICS" and "DISASSOCIATE STATISTICS".

Example

The following statement drops the function NEW_ACCT in the schema RIDDLEY and invalidates all objects that depend upon NEW_ACCT:

DROP FUNCTION riddley.new_acct;

DROP INDEX

Syntax

Purpose

To remove an index or domain index from the database.

For more information on indexes, see "CREATE INDEX" and "ALTER INDEX". For more information on domain indexes, see the domain_index_clause of "CREATE INDEX".

Prerequisites

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

Keywords and Parameters

schema

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

index

is the name of the index to be dropped. When the index is dropped, all data blocks allocated to the index are returned to the index's tablespace.
If you drop a domain index:

Oracle invokes the appropriate indextype drop routine. For information on these routines, see .

In addition, if any statistics are associated with the domain index, Oracle disassociates the statistics types with the FORCE clause and removes the user-defined statistics collected with the statistics type. For more information on statistics type associations, see "ASSOCIATE STATISTICS" and "DISASSOCIATE STATISTICS".

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

FORCE

applies only to domain indexes. This clause drops the domain index even if the indextype routine invocation returns an error or the index is marked LOADING. Without FORCE, you cannot drop a domain index if its indextype routine invocation returns an error or the index is marked LOADING.

Example

This statement drops an index named MONOLITH:

DROP INDEX monolith;

DROP INDEXTYPE

Syntax

Purpose

To drop an indextype, as well as any association with a statistics type.

For more information on indextypes, see "CREATE INDEXTYPE".

Prerequisites

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

Keywords and Parameters

schema

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

indextype

is the name of the indextype to be dropped.
If any statistics types have been associated with indextype, Oracle disassociates the statistics type from the indextype and drops any statistics that have been collected using the statistics type. For more information on statistics associations, see "ASSOCIATE STATISTICS" and "DISASSOCIATE STATISTICS".

FORCE

drops the indextype even if the indextype is currently being referenced by one or more domain indexes, and marks those domain indexes INVALID. Without FORCE, you cannot drop an indextype if any domain indexes reference the indextype.

Example

The following statement drops the indextype TEXTINDEXTYPE and marks INVALID any domain indexes defined on this indextype:

DROP INDEXTYPE textindextype FORCE;

DROP JAVA

Syntax

Purpose

To drop a Java source, class, or resource schema object.

For more information on resolving Java sources, classes, and resources, see Oracle8i Java Stored Procedures Developer's Guide.

Prerequisites

The Java source, class, or resource must be in your own schema or you must have the DROP ANY PROCEDURE system privilege. You also must have the EXECUTE object privilege on Java classes to use this command.

Keywords and Parameters

JAVA SOURCE

drops a Java source schema object and all Java class schema objects derived from it.

JAVA CLASS

drops a Java class schema object.

JAVA RESOURCE

drops a Java resource schema object.

object_name

specifies the name of an existing Java class, source, or resource schema object.

Example

The following statement drops the Java class MyClass:

DROP JAVA CLASS "MyClass";

DROP LIBRARY

Syntax

Purpose

To remove an external procedure library from the database.

For information on creating a library, see "CREATE LIBRARY".

Prerequisites

You must have the DROP LIBRARY system privilege.

Keywords and Parameters

library_name

is the name of the external procedure library being dropped.

Example

The following statement drops the EXT_PROCS library:

DROP LIBRARY ext_procs;

DROP MATERIALIZED VIEW / SNAPSHOT

Syntax

Purpose

To remove an existing materialized view from the database.

The terms "snapshot" and "materialized view" are synonymous. For more information on materialized views, including a description of the various types of materialized views, see "CREATE MATERIALIZED VIEW / SNAPSHOT". For information on materialized views in a replication environment, see Oracle8i Replication. For information on materialized views in a data warehousing environment, see Oracle8i Tuning.

Prerequisites

The materialized view must be in your own schema or you must have the DROP ANY MATERIALIZED VIEW (or DROP ANY SNAPSHOT) system privilege. You must also have the privileges to drop the internal table, views, and index that Oracle uses to maintain the materialized view's data.

For information on these privileges, see "DROP TABLE", "DROP VIEW", and "DROP INDEX".

Keywords and Parameters

schema

is the schema containing the materialized view. If you omit schema, Oracle assumes the materialized view is in your own schema.

materialized view / snapshot

is the name of the existing materialized view to be dropped.

If you drop a simple materialized view that is the least recently refreshed materialized view of a master table, Oracle automatically purges from the detail table's materialized view log only the rows needed to refresh the dropped materialized view.

If you drop a detail table, Oracle does not automatically drop materialized views based on the table. However, Oracle returns an error when it tries to refresh a materialized view based on a detail table that has been dropped.

If you drop a materialized view, any compiled requests that were rewritten to use the materialized view will be invalidated and recompiled automatically. If the materialized view was prebuilt on a table, the table is not dropped, but it can no longer be maintained by the materialized view refresh mechanism.

Examples

The following statement drops the materialized view PARTS owned by the user HQ:

DROP SNAPSHOT hq.parts;

The following statement drops the SALES_BY_MONTH materialized view and the underlying table of the materialized view (unless the underlying table was registered in the CREATE MATERIALIZED VIEW statement with the ON PREBUILT TABLE clause):

DROP MATERIALIZED VIEW sales_by_month;

DROP MATERIALIZED VIEW LOG / SNAPSHOT LOG

Syntax

Purpose

To remove a materialized view log from the database.

The terms "snapshot" and "materialized view" are synonymous. For more information on materialized views, including a description of the various types of materialized views and refreshing materialized views, see "CREATE MATERIALIZED VIEW / SNAPSHOT" and "ALTER MATERIALIZED VIEW / SNAPSHOT".

For information on materialized view logs, see "CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG".

For information on materialized views in a replication environment, see Oracle8i Replication. For information on materialized views in a data warehousing environment, see Oracle8i Tuning.

Prerequisites

A materialized view log consists of a table and a trigger. To drop a materialized view log, you must have the privileges listed for "DROP TABLE".

Keywords and Parameters

schema

is the schema containing the materialized view log and its master table. If you omit schema, Oracle assumes the materialized view log and master table are in your own schema.

table

is the name of the detail table associated with the materialized view log to be dropped.

After you drop a materialized view log, some materialized views based on the materialized view log's detail table can no longer be fast refreshed. These materialized views include rowid materialized views, primary key materialized views, and subquery materialized views. For a description of the types of materialized views, see .

Example

The following statement drops the materialized view log on the PARTS master table:

DROP MATERIALIZED VIEW LOG ON parts;

DROP OPERATOR

Syntax

Purpose

To drop a user-defined operator.

For more information on operators, see "User-Defined Operators", Oracle8i Data Cartridge Developer's Guide and "CREATE OPERATOR".

Prerequisites

The operator must be in your schema or you must have the DROP ANY OPERATOR system privilege.

Keywords and Parameters

schema

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

operator

specifies the name of the operator to be dropped.

FORCE

drops the operator even if it is currently being referenced by one or more schema objects (indextypes, packages, functions, procedures, and so on), and marks those dependent objects INVALID. Without FORCE, you cannot drop an operator if any schema objects reference it.

Example

The following statement drops the operator MERGE:

DROP OPERATOR ordsys.merge;

Because the FORCE clause is not specified, this operation will fail if any of the bindings of this operator are referenced by an indextype.

DROP OUTLINE

Syntax

Purpose

To drop a stored outline.

For more information on outlines, see "CREATE OUTLINE" and Oracle8i Tuning.

Prerequisites

To drop an outline, you must have the DROP ANY OUTLINE system privilege.

Keywords and Parameters

outline

is the name of the outline to be dropped.
After the outline is dropped, if the SQL statement for which the stored outline was created is compiled, the optimizer generates a new execution plan without the influence of the outline.

Example

The following statement drops the stored outline called SALARIES.

DROP OUTLINE salaries;

DROP PACKAGE

Syntax

Purpose

To remove a stored package from the database. This statement drops the body and specification of a package.

Note:
Do not use this statement to remove a single object from a package. Instead, re-create the package without the object using the CREATE PACKAGE and CREATE PACKAGE BODY statements with the OR REPLACE clause. See "CREATE PACKAGE".

Prerequisites

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

Keywords and Parameters

BODY

drops only the body of the package. If you omit this clause, Oracle drops both the body and specification of the package.
When you drop only the body of a package but not its specification, Oracle does not invalidate dependent objects. However, you cannot call one of the procedures or stored functions declared in the package specification until you re-create the package body.

schema

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

package

is the name of the package to be dropped.
Oracle invalidates any local objects that depend on the package specification. If you subsequently reference one of these objects, Oracle tries to recompile the object and returns an error if you have not re-created the dropped package. For information on how Oracle maintains dependencies among schema objects, including remote objects, see .
If any statistics types are associated with the package, Oracle disassociates the statistics types with the FORCE clause and drops any user-defined statistics collected with the statistics types. For more information, see "ASSOCIATE STATISTICS" and "DISASSOCIATE STATISTICS".

Example

The following statement drops the specification and body of the BANKING package, invalidating all objects that depend on the specification:

DROP PACKAGE banking;

DROP PROCEDURE

Syntax

Purpose

To remove a standalone stored procedure from the database. Do not use this statement to remove a procedure that is part of a package. Instead, either drop the entire package using the DROP PACKAGE statement, or redefine the package without the procedure using the CREATE PACKAGE statement with the OR REPLACE clause.

For information on creating a procedure, see "CREATE PROCEDURE".

Prerequisites

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

Keywords and Parameters

schema

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

procedure

is the name of the procedure to be dropped.
When you drop a procedure, Oracle invalidates any local objects that depend upon the dropped procedure. If you subsequently reference one of these objects, Oracle tries to recompile the object and returns an error message if you have not re-created the dropped procedure. For information on how Oracle maintains dependencies among schema objects, including remote objects, see .

Example

The following statement drops the procedure TRANSFER owned by the user KERNER and invalidates all objects that depend upon TRANSFER:

DROP PROCEDURE kerner.transfer

DROP PROFILE

Syntax

Purpose

To remove a profile from the database.

For information on creating a profile, see "CREATE PROFILE".

Prerequisites

You must have the DROP PROFILE system privilege.

Keywords and Parameters

profile

is the name of the profile to be dropped.
Restriction: You cannot drop the DEFAULT profile.

CASCADE

deassigns the profile from any users to whom it is assigned. Oracle automatically assigns the DEFAULT profile to such users. You must specify this clause to drop a profile that is currently assigned to users.

Example

The following statement drops the profile ENGINEER:

DROP PROFILE engineer CASCADE;

Oracle drops the profile ENGINEER and assigns the DEFAULT profile to any users currently assigned the ENGINEER profile.

DROP ROLE

Syntax

Purpose

To remove a role from the database. When you drop a role, Oracle revokes it from all users and roles to whom it has been granted and removes it from the database.

For information on creating roles, see "CREATE ROLE". For information on disabling roles for the current session, see "SET ROLE".

Prerequisites

You must have been granted the role with the ADMIN OPTION or you must have the DROP ANY ROLE system privilege.

Keywords and Parameters

role

is the role to be dropped.

Example

To drop the role FLORIST, issue the following statement:

DROP ROLE florist;

DROP ROLLBACK SEGMENT

Syntax

Purpose

To remove a rollback segment from the database. When you drop a rollback segment, all space allocated to the rollback segment returns to the tablespace.

For information on creating a rollback segment, see CREATE ROLLBACK SEGMENT . See also "CREATE TABLESPACE".

Prerequisites

You must have the DROP ROLLBACK SEGMENT system privilege.

Keywords and Parameters

rollback_segment

is the name the rollback segment to be dropped.
Restrictions:

You can drop a rollback segment only if it is offline. To determine whether a rollback segment is offline, query the data dictionary view DBA_ROLLBACK_SEGS. Offline rollback segments have the value AVAILABLE in the STATUS column. You can take a rollback segment offline with the OFFLINE clause of the ALTER ROLLBACK SEGMENT statement.

You cannot drop the SYSTEM rollback segment.

Example

The following statement drops the rollback segment ACCOUNTING:

DROP ROLLBACK SEGMENT accounting;

DROP SEQUENCE

Syntax

Purpose

To remove a sequence from the database.

You can also use this statement to restart a sequence by dropping and then re-creating it. For example, if you have a sequence with a current value of 150 and you would like to restart the sequence with a value of 27, you can drop the sequence and then re-create it with the same name and a START WITH value of 27.

For more information on creating and modifying sequences, see "CREATE SEQUENCE" and "ALTER SEQUENCE".

Prerequisites

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

Keywords and Parameters

schema

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

sequence_name

is the name of the sequence to be dropped.

Example

The following statement drops the sequence ESEQ owned by the user ELLY. To issue this statement, you must either be connected as user ELLY or have DROP ANY SEQUENCE system privilege:

DROP SEQUENCE elly.eseq;

DROP SNAPSHOT

In Oracle8i, "snapshots" are synonymous with "materialized views." Please see "DROP MATERIALIZED VIEW / SNAPSHOT".

DROP SNAPSHOT LOG

In Oracle8i, "snapshots" are synonymous with "materialized views." Please see "DROP MATERIALIZED VIEW LOG / SNAPSHOT LOG".

DROP SYNONYM

Syntax

Purpose

To remove a synonym from the database, or to change the definition of a synonym by dropping and re-creating it.

For more information on synonyms, see "CREATE SYNONYM".

Prerequisites

To drop a private synonym, either the synonym must be in your own schema or you must have the DROP ANY SYNONYM system privilege.

To drop a PUBLIC synonym, either the synonym must be in your own schema or you must have the DROP ANY PUBLIC SYNONYM system privilege.

Keywords and Parameters

PUBLIC

must be specified to drop a public synonym. You cannot specify schema if you have specified PUBLIC.

schema

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

synonym

is the name of the synonym to be dropped.
If you drop a synonym for a materialized view, or its containing table or snapshot, or any of its dependent tables, the materialized view will be invalidated.

Example

To drop a synonym named MARKET, issue the following statement:

DROP SYNONYM market;

DROP TABLE

Syntax

Purpose

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

For information on creating tables, see "CREATE TABLE". For information on modifying tables, see "ALTER TABLE".

Prerequisites

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

Keywords and Parameters

schema

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

table

is 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. For more information on these routines, see .

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. For more information on statistics type associations, see "ASSOCIATE STATISTICS" and "DISASSOCIATE STATISTICS".

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.

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/snapshot log, Oracle drops this log and any other direct-load INSERT refresh information associated with the table.

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".

CASCADE CONSTRAINTS

drops 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

The following statement drops the TEST_DATA table:

DROP TABLE test_data;

DROP TABLESPACE

Syntax

Purpose

To remove a tablespace from the database.

For information on creating and modifying tablespaces, see "CREATE TABLESPACE" and "ALTER TABLESPACE".

Prerequisites

You must have the DROP TABLESPACE system privilege. You cannot drop a tablespace if it contains any rollback segments holding active transactions.

Keywords and parameters

tablespace

is the name of the tablespace to be dropped.

You can drop a tablespace regardless of whether it is online or offline. Oracle recommends that you take the tablespace offline before dropping it to ensure that no SQL statements in currently running transactions access any of the objects in the tablespace.

You may want to alert any users who have been assigned the tablespace as either a default or temporary tablespace. After the tablespace has been dropped, these users cannot allocate space for objects or sort areas in the tablespace. You can reassign users new default and temporary tablespaces with the ALTER USER statement.

Restrictions:

You cannot drop the SYSTEM tablespace.

You cannot drop a tablespace that contains a domain index or any objects created by a domain index. For more information on domain indexes, see and .

INCLUDING CONTENTS

drops all the contents of the tablespace. You must specify this clause to drop a tablespace that contains any database objects. If you omit this clause, and the tablespace is not empty, Oracle returns an error and does not drop the tablespace.

For partitioned tables, DROP TABLESPACE will fail even if you specify INCLUDING CONTENTS, if the tablespace contains some, but not all,

partitions of a range- or hash-partitioned table, or

subpartitions of a composite-partitioned table.

Note: If all the partitions of a partitioned table reside in tablespace, DROP TABLESPACE ... INCLUDING CONTENTS will drop tablespace, as well as any associated index segments, LOB data segments, and LOB index segments in the other tablespace(s).

For a partitioned index-organized table, if all the primary key index segments are in this tablespace, this clause will also drop any overflow segments that exist in other tablespaces. If some of the primary key index segments are not in this tablespace, the statement will fail. In that case, before you can drop the tablespace, you must use ALTER TABLE ... MOVE PARTITION to move those primary key index segments into this tablespace, drop the partitions whose overflow data segments are not in this tablespace, and drop the partitioned index-organized table.
If the tablespace contains a container table or detail table of a materialized view, Oracle invalidates the materialized view.

If the tablespace contains a materialized view/snapshot log, Oracle drops this log and any other direct-load INSERT refresh information associated with the table.

CASCADE CONSTRAINTS

drops all referential integrity constraints from tables outside tablespace that refer to primary and unique keys of tables inside tablespace. If you omit this clause and such referential integrity constraints exist, Oracle returns an error and does not drop the tablespace.

Example

The following statement drops the MFRG tablespace and all its contents:

DROP TABLESPACE mfrg 
    INCLUDING CONTENTS 
        CASCADE CONSTRAINTS;

DROP TRIGGER

Syntax

Purpose

To remove a database trigger from the database.

For information on creating triggers, see "CREATE TRIGGER".

Prerequisites

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

In addition, to drop a trigger on DATABASE in another user's schema, you must have the ADMINISTER DATABASE TRIGGER system privilege. For more information on database triggers, see "CREATE TRIGGER".

Keywords and Parameters

schema

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

trigger

is the name of the trigger to be dropped. Oracle removes it from the database and does not fire it again.

Example

The following statement drops the REORDER trigger in the schema RUTH:

DROP TRIGGER ruth.reorder;

DROP TYPE

Syntax

Purpose

To drop the specification and body of an object, a varray, or nested table type. To drop just the body of an object type, see "DROP TYPE BODY".

For more information on types, see "CREATE TYPE".

Prerequisites

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

Keywords and Parameters

schema

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

type_name

is 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. For more information on statistics types, see "ASSOCIATE STATISTICS" and "DISASSOCIATE STATISTICS".

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. For more information, see "CREATE INDEXTYPE".
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.

FORCE

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

WARNING: 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 .

Example

The following statement removes object type PERSON_T:

DROP TYPE person_t;

DROP TYPE BODY

Syntax

Purpose

To drop the body of an object, varray, or nested table type. When you drop a type body, the object type specification still exists, and you can re-create the type body. Prior to re-creating the body, you can still use the object type, although you cannot call the member functions.

To drop the specification of an object, see "DROP TYPE". For more information on type bodies, see "CREATE TYPE BODY".

Prerequisites

The object type body must be in your own schema, and you must have

the CREATE TYPE or CREATE ANY TYPE system privilege, or
the DROP ANY TYPE system privilege

Keywords and Parameters

schema

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

type_name

is the name of the object type body to be dropped.
Restriction: You can drop a type body only if it has no type or table dependencies.

Example

The following statement removes object type body RATIONAL:

DROP TYPE BODY rational;

DROP USER

Syntax

Purpose

To remove a database user and optionally remove the user's objects. For information on creating a user, see "CREATE USER". For information on modifying the definition of a user, see "ALTER USER".

Prerequisites

You must have the DROP USER system privilege.

Keywords and Parameters

user

is the user to be dropped. Oracle does not drop users whose schemas contain objects unless you specify CASCADE, or unless you first explicitly drop the user's objects.

CASCADE

drops all objects in the user's schema before dropping the user. You must specify this clause to drop a user whose schema contains any objects.

If the user's schema contains tables, Oracle drops the tables and automatically drops any referential integrity constraints on tables in other schemas that refer to primary and unique keys on these tables.

If this clause results in tables being dropped, Oracle also drops all domain indexes created on columns of those tables, and invokes appropriate drop routines. For more information on these routines, see .

Oracle invalidates, but does not drop, the following objects in other schemas: views or synonyms for objects in the dropped user's schema; and stored procedures, functions, or packages that query objects in the dropped user's schema.

Oracle does not drop materialized views on tables or views in the dropped user's schema, but if you specify CASCADE, the materialized views can no longer be refreshed.

Oracle drops all triggers in the user's schema.

Oracle does not drop roles created by the user.

WARNING: Oracle also drops with FORCE all types owned by the user. See the FORCE keyword of "DROP TYPE".

Examples

If user BRADLEY's schema contains no objects, you can drop BRADLEY by issuing the statement:

DROP USER bradley;

If BRADLEY's schema contains objects, you must use the CASCADE clause to drop BRADLEY and the objects:

DROP USER bradley CASCADE;

DROP VIEW

Syntax

Purpose

To remove a view or an object view from the database. You can change the definition of a view by dropping and re-creating it. For more information, see "CREATE VIEW".

Prerequisites

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

Keywords and Parameters

schema

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

view

is the name of the view to be dropped.
Views, materialized views, and synonyms that refer to the view are not dropped, but become invalid. You can drop them or redefine views and synonyms, or you can define other views in such a way that the invalid views and synonyms become valid again. See "CREATE TABLE" and "CREATE SYNONYM". To revalidate invalid materialized views, see "ALTER MATERIALIZED VIEW / SNAPSHOT".

Example

The following statement drops the VIEW_DATA view:

DROP VIEW view_data;

EXPLAIN PLAN

Syntax

Purpose

To determine the execution plan Oracle follows to execute a specified SQL statement. This statement inserts a row describing each step of the execution plan into a specified table. If you are using cost-based optimization, this statement also determines the cost of executing the statement. If any domain indexes are defined on the table, user-defined CPU and I/O costs will also be inserted. See Oracle8i Tuning for information on the output of EXPLAIN PLAN.

The definition of a sample output table PLAN_TABLE is available in a SQL script on your distribution media. Your output table must have the same column names and datatypes as this table. The common name of this script is UTLXPLAN.SQL. The exact name and location depend on your operating system.

Note:
Do not use the EXPLAIN PLAN statement to determine the execution plans of SQL statements that access data dictionary views or dynamic performance tables.

You can also issue the EXPLAIN PLAN statement as part of the SQL trace facility. For information on how to use the SQL trace facility, as well as a detailed discussion of how to generate and interpret execution plans, see Oracle8i Tuning.

Prerequisites

To issue an EXPLAIN PLAN statement, you must have the privileges necessary to insert rows into an existing output table that you specify to hold the execution plan. For information on these privileges, see "INSERT".

You must also have the privileges necessary to execute the SQL statement for which you are determining the execution plan. If the SQL statement accesses a view, you must have privileges to access any tables and views on which the view is based. If the view is based on another view that is based on a table, you must have privileges to access both the other view and its underlying table.

To examine the execution plan produced by an EXPLAIN PLAN statement, you must have the privileges necessary to query the output table. For more information on these privileges, see "SELECT and Subqueries".

The EXPLAIN PLAN statement is a data manipulation language (DML) statement, rather than a data definition language (DDL) statement. Therefore, Oracle does not implicitly commit the changes made by an EXPLAIN PLAN statement. If you want to keep the rows generated by an EXPLAIN PLAN statement in the output table, you must commit the transaction containing the statement.

Keywords and Parameters

SET STATEMENT_ID

specifies the value of the STATEMENT_ID column for the rows of the execution plan in the output table. You can then use this value to identify these rows among others in the output table. Be sure to specify a STATEMENT_ID value if your output table contains rows from many execution plans. If you omit this clause, the STATEMENT_ID value defaults to null.

INTO

specifies name of the output table, and optionally its schema and database. This table must exist before you use the EXPLAIN PLAN statement.
If you omit schema, Oracle assumes the table is in your own schema.

The dblink can be a complete or partial name of a database link to a remote Oracle database where the output table is located. For information on referring to database links, see the section, "Referring to Objects in Remote Databases". You can specify a remote output table only if you are using Oracle's distributed functionality. If you omit dblink, Oracle assumes the table is on your local database.

If you omit INTO altogether, Oracle assumes an output table named PLAN_TABLE in your own schema on your local database.

FOR statement

specifies a SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, or CREATE INDEX statement for which the execution plan is generated.

Note: If statement includes the parallel_clause, the resulting execution plan will indicate parallel execution. However, EXPLAIN PLAN actually inserts the statement into the plan table, so that the parallel DML statement you submit is no longer the first DML statement in the transaction. This violates the Oracle restriction of one parallel DML statement per transaction, and the statement will be executed serially. To maintain parallel execution of the statements, you must commit or roll back the EXPLAIN PLAN statement, and then submit the parallel DML statement.

Examples

The following statement determines the execution plan and cost for an UPDATE statement and inserts rows describing the execution plan into the specified OUTPUT table with the STATEMENT_ID value of 'Raise in Chicago':

EXPLAIN PLAN 
    SET STATEMENT_ID = 'Raise in Chicago' 
    INTO output 
    FOR UPDATE emp 
        SET sal = sal * 1.10 
        WHERE deptno =  (SELECT deptno 
                  FROM dept
                  WHERE loc = 'CHICAGO');

The following SELECT statement queries the OUTPUT table and returns the execution plan and the cost:

SELECT LPAD(' ',2*(LEVEL-1))||operation operation, options, 
object_name, position 
    FROM output 
    START WITH id = 0 AND statement_id = 'Raise in Chicago'
    CONNECT BY PRIOR id = parent_id AND 
    statement_id = 'Raise in Chicago';

The query returns this execution plan:

OPERATION             OPTIONS     OBJECT_NAME     POSITION
----------------------------------------------------------
UPDATE STATEMENT                                         1
    FILTER                                               0
      TABLE ACCESS    FULL        EMP                    1
      TABLE ACCESS    FULL        DEPT                   2

The value in the POSITION column of the first row shows that the statement has a cost of 1.

Partitioned Example

Assume that STOCKS is a table with eight partitions on a STOCK_NUM column, and that a local prefixed index STOCK_IX on column STOCK_NUM exists. The partition HIGHVALUES are 1000, 2000, 3000, 4000, 5000, 6000, 7000, and 8000.

Consider the query:

SELECT * FROM stocks WHERE stock_num BETWEEN 3800 AND :h;

(where :h represents a bind variable). EXPLAIN PLAN executes this query with PLAN_TABLE as the output table. The basic execution plan, including partitioning information, is obtained with the query:

SELECT id, operation, options, object_name,
  partition_start, partition_stop, partition_id FROM plan_table;

Click the "Next" button to see the next statement in this chapter.

schema	is the name of the schema in which the dimension is located. If you omit schema, Oracle assumes the dimension is in your own schema.
dimension	is the name of the dimension you want to drop. The dimension must already exist.
	This statement does not invalidate materialized views that use relationships specified in dimension. However, requests that have been rewritten by query rewrite may be invalidated, and subsequent operations on such views may execute more slowly.

schema	is the schema containing the index. If you omit schema, Oracle assumes the index is in your own schema.
index	is the name of the index to be dropped. When the index is dropped, all data blocks allocated to the index are returned to the index's tablespace. If you drop a domain index: Oracle invokes the appropriate indextype drop routine. For information on these routines, see . In addition, if any statistics are associated with the domain index, Oracle disassociates the statistics types with the FORCE clause and removes the user-defined statistics collected with the statistics type. For more information on statistics type associations, see "ASSOCIATE STATISTICS" and "DISASSOCIATE STATISTICS".
	If you drop a global partitioned index, a range-partitioned, or a hash-partitioned index, all the index partitions are also dropped. If you drop a a composite-partitioned index, all the index partitions and subpartitions are also dropped.
FORCE	applies only to domain indexes. This clause drops the domain index even if the indextype routine invocation returns an error or the index is marked LOADING. Without FORCE, you cannot drop a domain index if its indextype routine invocation returns an error or the index is marked LOADING.

JAVA SOURCE	drops a Java source schema object and all Java class schema objects derived from it.
JAVA CLASS	drops a Java class schema object.
JAVA RESOURCE	drops a Java resource schema object.
object_name	specifies the name of an existing Java class, source, or resource schema object.

schema	is the schema containing the materialized view. If you omit schema, Oracle assumes the materialized view is in your own schema.
materialized view / snapshot	is the name of the existing materialized view to be dropped.
	If you drop a simple materialized view that is the least recently refreshed materialized view of a master table, Oracle automatically purges from the detail table's materialized view log only the rows needed to refresh the dropped materialized view.
	If you drop a detail table, Oracle does not automatically drop materialized views based on the table. However, Oracle returns an error when it tries to refresh a materialized view based on a detail table that has been dropped.
	If you drop a materialized view, any compiled requests that were rewritten to use the materialized view will be invalidated and recompiled automatically. If the materialized view was prebuilt on a table, the table is not dropped, but it can no longer be maintained by the materialized view refresh mechanism.

schema	is the schema containing the materialized view log and its master table. If you omit schema, Oracle assumes the materialized view log and master table are in your own schema.
table	is the name of the detail table associated with the materialized view log to be dropped.
	After you drop a materialized view log, some materialized views based on the materialized view log's detail table can no longer be fast refreshed. These materialized views include rowid materialized views, primary key materialized views, and subquery materialized views. For a description of the types of materialized views, see .

schema	is the schema containing the operator. If you omit schema, Oracle assumes the operator is in your own schema.
operator	specifies the name of the operator to be dropped.
FORCE	drops the operator even if it is currently being referenced by one or more schema objects (indextypes, packages, functions, procedures, and so on), and marks those dependent objects INVALID. Without FORCE, you cannot drop an operator if any schema objects reference it.

schema	is the schema containing the sequence. If you omit schema, Oracle assumes the sequence is in your own schema.
sequence_name	is the name of the sequence to be dropped.

schema	is the schema containing the table. If you omit schema, Oracle assumes the table is in your own schema.
table	is 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. For more information on these routines, see .
	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. For more information on statistics type associations, see "ASSOCIATE STATISTICS" and "DISASSOCIATE STATISTICS".
	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. 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/snapshot log, Oracle drops this log and any other direct-load INSERT refresh information associated with the table.
	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".
CASCADE CONSTRAINTS	drops 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.

tablespace	is the name of the tablespace to be dropped.
	You can drop a tablespace regardless of whether it is online or offline. Oracle recommends that you take the tablespace offline before dropping it to ensure that no SQL statements in currently running transactions access any of the objects in the tablespace.
	You may want to alert any users who have been assigned the tablespace as either a default or temporary tablespace. After the tablespace has been dropped, these users cannot allocate space for objects or sort areas in the tablespace. You can reassign users new default and temporary tablespaces with the ALTER USER statement.
	Restrictions: You cannot drop the SYSTEM tablespace. You cannot drop a tablespace that contains a domain index or any objects created by a domain index. For more information on domain indexes, see and .
INCLUDING CONTENTS	drops all the contents of the tablespace. You must specify this clause to drop a tablespace that contains any database objects. If you omit this clause, and the tablespace is not empty, Oracle returns an error and does not drop the tablespace.
	For partitioned tables, DROP TABLESPACE will fail even if you specify INCLUDING CONTENTS, if the tablespace contains some, but not all, partitions of a range- or hash-partitioned table, or subpartitions of a composite-partitioned table. Note: If all the partitions of a partitioned table reside in tablespace, DROP TABLESPACE ... INCLUDING CONTENTS will drop tablespace, as well as any associated index segments, LOB data segments, and LOB index segments in the other tablespace(s). For a partitioned index-organized table, if all the primary key index segments are in this tablespace, this clause will also drop any overflow segments that exist in other tablespaces. If some of the primary key index segments are not in this tablespace, the statement will fail. In that case, before you can drop the tablespace, you must use ALTER TABLE ... MOVE PARTITION to move those primary key index segments into this tablespace, drop the partitions whose overflow data segments are not in this tablespace, and drop the partitioned index-organized table. If the tablespace contains a container table or detail table of a materialized view, Oracle invalidates the materialized view.
	If the tablespace contains a materialized view/snapshot log, Oracle drops this log and any other direct-load INSERT refresh information associated with the table.
CASCADE CONSTRAINTS	drops all referential integrity constraints from tables outside tablespace that refer to primary and unique keys of tables inside tablespace. If you omit this clause and such referential integrity constraints exist, Oracle returns an error and does not drop the tablespace.

schema	is the schema containing the trigger. If you omit schema, Oracle assumes the trigger is in your own schema.
trigger	is the name of the trigger to be dropped. Oracle removes it from the database and does not fire it again.

schema	is the schema containing the type. If you omit schema, Oracle assumes the type is in your own schema.
type_name	is 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. For more information on statistics types, see "ASSOCIATE STATISTICS" and "DISASSOCIATE STATISTICS".
	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. For more information, see "CREATE INDEXTYPE". 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.
FORCE	forces the type to be dropped even if it has dependent database objects. Oracle marks UNUSED all columns dependent on the type to be dropped, and those columns become inaccessible.
	WARNING: 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 .

SET STATEMENT_ID	specifies the value of the STATEMENT_ID column for the rows of the execution plan in the output table. You can then use this value to identify these rows among others in the output table. Be sure to specify a STATEMENT_ID value if your output table contains rows from many execution plans. If you omit this clause, the STATEMENT_ID value defaults to null.
INTO	specifies name of the output table, and optionally its schema and database. This table must exist before you use the EXPLAIN PLAN statement. If you omit schema, Oracle assumes the table is in your own schema.
	The dblink can be a complete or partial name of a database link to a remote Oracle database where the output table is located. For information on referring to database links, see the section, "Referring to Objects in Remote Databases". You can specify a remote output table only if you are using Oracle's distributed functionality. If you omit dblink, Oracle assumes the table is on your local database.
	If you omit INTO altogether, Oracle assumes an output table named PLAN_TABLE in your own schema on your local database.
FOR statement	specifies a SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, or CREATE INDEX statement for which the execution plan is generated.
	Note: If statement includes the parallel_clause, the resulting execution plan will indicate parallel execution. However, EXPLAIN PLAN actually inserts the statement into the plan table, so that the parallel DML statement you submit is no longer the first DML statement in the transaction. This violates the Oracle restriction of one parallel DML statement per transaction, and the statement will be executed serially. To maintain parallel execution of the statements, you must commit or roll back the EXPLAIN PLAN statement, and then submit the parallel DML statement.

7SQL Statements (continued)

DROP DATABASE LINK

Syntax

Purpose

Prerequisites

Keywords and Parameters

Example

DROP DIMENSION

Syntax

Purpose

Prerequisites

Keywords and Parameters

Example

DROP DIRECTORY

Syntax

Purpose

Prerequisites

Keywords and Parameters

Example

DROP FUNCTION

Syntax

Purpose

Prerequisites

Keywords and Parameters

Example

DROP INDEX

Syntax

Purpose

Prerequisites

Keywords and Parameters

Example

DROP INDEXTYPE

Syntax

Purpose

Prerequisites

Keywords and Parameters

Example

DROP JAVA

Syntax

Purpose

Prerequisites

Keywords and Parameters

Example

DROP LIBRARY

Syntax

Purpose

Prerequisites

Keywords and Parameters

Example

DROP MATERIALIZED VIEW / SNAPSHOT

Syntax

Purpose

Prerequisites

Keywords and Parameters

Examples

DROP MATERIALIZED VIEW LOG / SNAPSHOT LOG

Syntax

Purpose

Prerequisites

Keywords and Parameters

Example

DROP OPERATOR

Syntax

Purpose

Prerequisites

Keywords and Parameters

Example

DROP OUTLINE

Syntax

Purpose

Prerequisites

Keywords and Parameters

Example

DROP PACKAGE

Syntax

Purpose

Prerequisites

Keywords and Parameters

Example

DROP PROCEDURE

7
SQL Statements (continued)