ORA-02429

SQL> drop tablespace CMS_DYY_DATA including contents and datafiles;
drop tablespace CMS_DYY_DATA 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
oracle@linux-qfcb:~> oerr ora 02429
02429, 00000, "cannot drop index used for enforcement of unique/primary key"
// *Cause: user attempted to drop an index that is being used as the
//         enforcement mechanism for unique or primary key.
// *Action: drop the constraint instead of the index.

SQL> drop user GZYYCMS cascade;

User dropped.

SQL> drop tablespace CMS_DYY_DATA including contents and datafiles;

Tablespace dropped.

偷了一个懒，直接把该用户下的所有表，视图都删除掉，然后再来删除表空间。
正确应该是先找出该表空间里面有哪些unique or primary key然后删除。
  select ' alter table '||owner||'.'||table_name||'drop constraint'||constraint_name||';'
  from dba_constraints
  where constraint_type in('U','P')
  and (index_owner,index_name) in
  (select owner,segment_name from dba_segments
  where tablespace_name=' TABLESPACE_NAME')

注：TABLESPACE_NAME 为需要删除的表空间名。

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

转载于:http://blog.itpub.net/22779291/viewspace-704063/