ora-00604 ora-02429

问题

删除表空间报错

oerr ora 00604
00604, 00000, "error occurred at recursive SQL level %s"
// *Cause:  An error occurred while processing a recursive SQL statement
//         (a statement applying to internal dictionary tables).
// *Action: If the situation described in the next error on the stack
//         can be corrected, do so; otherwise contact Oracle Support.

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.

解决思路

select * from (select owner,segment_name,segment_type,sum(bytes)/1024/1024/1024 GB  
        from dba_segments 
        where tablespace_name='表空间名称大写'
        group by owner,segment_name,segment_type order by 4 desc ) 
where rownum <10;

oerr ora 01418
01418, 00000, "specified index does not exist"
// *Cause:
// *Action:

大写也不好使。。。

set long 9999
select dbms_metadata.get_ddl('INDEX','索引名称','用户') from dual;

删除约束

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 = '表空间名称');

oerr ora 02443
02443, 00000, "Cannot drop constraint  - nonexistent constraint"
// *Cause: alter table drop constraint <constraint_name>
// *Action: make sure you supply correct constraint name.

注意要大写

再删除索引就OK了