只问耕耘
分类: Oracle
2007-08-20 10:48:10
DROP
TABLESPACE
statement to remove a tablespace from the database.
You must have the DROP
TABLESPACE
system privilege. You cannot drop a tablespace if it contains any rollback segments holding active transactions.
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:
SYSTEM
tablespace.
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.
For partitioned tables, DROP
TABLESPACE
will fail even if you specify INCLUDING
CONTENTS
, if the tablespace contains some, but not all,
Note: If all the partitions of a partitioned table reside in |
|
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.
DROP
TABLESPACE
Example
The following statement drops the mfrg
tablespace and all its contents:
DROP TABLESPACE mfrg
INCLUDING CONTENTS
CASCADE CONSTRAINTS;