drop tablespace in 11G

今天删除表空间报错：

sys@yhdstd> select segment_name from dba_segments where tablespace_name ='TINDEX';

no rows selected

sys@yhdstd> drop tablespace TINDEX including contents and datafiles;
drop tablespace TINDEX including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key

按理说我已经check了dba_segments，已经没有segment在表空间TINDEX里了。在10G里我都是这么操作的，从来没有遇到过问题。
在网上查了一些资料，意思都是说里面还有其他表空间的表用到的索引，于是检查了一下dba_indexes和dba_tables，
还真发现有索引属于表空间TINDEX，于是rebuild到另外的表空间后，成功drop了该表空间：

 

sys@yhdstd> alter index TEST.PK_AAMESSAGE_LOG rebuild online  tablespace TTUSERS;

Index altered.

sys@yhdstd> alter index TEST.PK_AAPAYTYPE rebuild online  tablespace TTUSERS;

Index altered.

sys@yhdstd> alter index TEST.PK_AAPOLICY_FETCH_CFG rebuild online  tablespace TTUSERS;

Index altered.

sys@yhdstd> alter index TEST.IDX_AAMESSAGE_LOG_SEND_TIME rebuild online  tablespace TTUSERS;

Index altered.

sys@yhdstd> select * from dba_indexes a where a.TABLESPACE_NAME='TINDEX';

no rows selected

sys@yhdstd> select * from dba_tables a where a.TABLESPACE_NAME='TINDEX';

no rows selected

sys@yhdstd> select segment_name from dba_segments where tablespace_name ='TINDEX';

no rows selected

sys@yhdstd> drop tablespace TINDEX including contents and datafiles;

Tablespace dropped.

 

究其因还是11G的特性deferred_segment_creation引起这些索引的segment没有创建，因为这几个表的数据为0：

sys@yhdstd> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

sys@yhdstd> show parameter segment

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE

 

The advantages of this new space allocation method are:

? A significant amount of disk space can be saved for applications that create hundreds or thousands of tables upon installation, many of which might never be populated.

? The application installation time is reduced, because the creation of a table is a data dictionary operation only.

When you insert the first row into the table, the segments are created for the base table, its LOB columns, and its indexes. During segment creation, cursors on the table are invalidated. These operations have a small additional impact on performance.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/758322/viewspace-740532/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/758322/viewspace-740532/