mysql drop tablespace,Oracle - 删除表空间 (DROP TABLESPACE)

Use the DROP TABLESPACE statement to remove a tablespace from the database.

Prerequisites

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

Syntax

Keywords and parameters

tablespace

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

See Also:Oracle8i Data Cartridge Developer's Guide and for more information on domain indexes

INCLUDING CONTENTS

Specify INCLUDING CONTENTS to

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

Forpartitioned 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

Specify CASCADE CONSTRAINTS to drop 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

DROP TABLESPACE Example

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

DROP TABLESPACE mfrg

INCLUDING CONTENTS

CASCADE CONSTRAINTS;