drop tablespace的注意事项

除了SYSTEM和SYSAUX不能删, 其它都是可以删除的. 如果删除不了, 一定是设置有问题.
Restrictions on Dropping Tablespaces Dropping tablespaces is subject to the following restrictions:

• You cannot drop a tablespace that contains a domain index or any objects created by a domain index.

• You cannot drop an undo tablespace if it is being used by any instance or if it contains any undo data needed to roll back uncommitted transactions.

• You cannot drop a tablespace that has been designated as the default tablespace for the database. You must first reassign another tablespace as the default tablespace and then drop the old default tablespace.

• You cannot drop a temporary tablespace if it is part of the database default temporary tablespace group. You must first remove the tablespace from the database default temporary tablespace group and then drop it.

• You cannot drop a tablespace, even with the INCLUDING CONTENTS and CASCADE CONSTRAINTS clauses, if doing so would disable a primary key or unique constraint in another tablespace. For example, if the tablespace being dropped contains a primary key index, but the primary key column itself is in a different tablespace, then you cannot drop the tablespace until you have manually disabled the primary key constraint in the other tablespace.

对于第二种情况, 可以采用的方法是

alter database default tablespace xx;
改变默认的表空间.

select default_tablespace, temporary_tablespace from dba_users;