ORA-01555和ORA-02429

今天晚上删除表空间时遇到的情况：

SQL> drop tablespace ts_work including contents ;
drop tablespace ts_work including contents
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too
small

后查询undo tablespace的空间，只有276M；
SQL> ho ls -lah /oracle/oradata/waterdb/undotbs01.dbf
-rw-r----- 1 oracle oinstall 276M Aug  2 21:18 /oracle/oradata/waterdb/undotbs01.dbf

果然把undo tablespace加大到5G就OK了。
SQL> ALTER DATABASE DATAFILE '/oracle/oradata/waterdb/undotbs01.dbf' resize 5G;
Database altered.



继续操作，又遇到错误了

SQL> drop tablespace ts_work including contents and datafiles;
drop tablespace ts_work 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

经参考http://www.cnblogs.com/umlzhang/archive/2010/09/10/1823213.html的文章后解决。
通过以下SQL语句查询引起问题的unique/primary key
SQL> 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 = upper('ts_work'));
  2    3    4    5    6    7    8    9  

'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'DROPCONSTRAINT'||CONSTRAINT_NAME||';'
--------------------------------------------------------------------------------------------------------------------------
alter table APTS.DY_TRIPLOG drop constraint SYS_C009486 ;

SQL> alter table APTS.DY_TRIPLOG drop constraint SYS_C009486 ;
Table altered.

SQL> drop tablespace ts_work including contents and datafiles cascade constraints;
Tablespace dropped.