Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 24 of 30
To remove a tablespace from the database.
For information on creating and modifying tablespaces, see "CREATE TABLESPACE" and "ALTER TABLESPACE".
You must have the DROP
TABLESPACE
system privilege. You cannot drop a tablespace if it contains any rollback segments holding active transactions.
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 |
|
Restrictions:
See Also: Oracle8i Data Cartridge Developer's Guide and Oracle8i Concepts for more information on domain indexes. |
|
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,
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 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 |
|
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. |
The following statement drops the MFRG
tablespace and all its contents:
DROP TABLESPACE mfrg INCLUDING CONTENTS CASCADE CONSTRAINTS;
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|