ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-02429

ORA-02429

原创 Linux操作系统 作者:frog-qs 时间:2011-08-04 10:26:11 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-10-27

  • 博文量
    132
  • 访问量
    244135