DROP TABLESPACE

Purpose

Prerequisites

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

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

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

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

Syntax

tablespace Keywords and Parameters

is the name of the tablespace to be dropped. 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, Oracle7 returns an error message and does not drop the tablespace.

CASCADE CONSTRAINTS

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

Usage Notes

You may want to alter 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 command.

You cannot drop the SYSTEM tablespace.

Example

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

DROP TABLESPACE mfrg 

	INCLUDING CONTENTS 

		CASCADE CONSTRAINTS 

Related Topics