执行大数据量删除操作时报如下错误
ORA-01552: cannot use system rollback segment for non-system tablespace 'lucky'。
查看回滚段管理模式和回滚表空间:
[oracle@server ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.5.0 - Production on Sun Jan 1 13:13:01 2012 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. SQL> conn /as sysdba Connected. SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string MANUAL undo_retention integer 900 undo_tablespace string SQL> select segment_name,tablespace_name,status from dba_rollback_segs; SEGMENT_NAME TABLESPACE_NAME STATUS ------------------------------ ------------------------------ ---------------- SYSTEM SYSTEM ONLINE _SYSSMU1$ UNDOTBS1 OFFLINE _SYSSMU2$ UNDOTBS1 OFFLINE _SYSSMU3$ UNDOTBS1 OFFLINE _SYSSMU4$ UNDOTBS1 OFFLINE _SYSSMU5$ UNDOTBS1 OFFLINE _SYSSMU6$ UNDOTBS1 OFFLINE _SYSSMU7$ UNDOTBS1 OFFLINE _SYSSMU8$ UNDOTBS1 OFFLINE _SYSSMU9$ UNDOTBS1 OFFLINE _SYSSMU10$ UNDOTBS1 OFFLINE _SYSSMU11$ UNDOTBS1 OFFLINE _SYSSMU12$ UNDOTBS1 OFFLINE _SYSSMU13$ UNDOTBS1 OFFLINE _SYSSMU14$ UNDOTBS1 OFFLINE _SYSSMU15$ UNDOTBS1 OFFLINE 16 rows selected 处理方法: SQL> alter system set undo_management=auto scope=spfile; System altered. SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string MANUAL undo_retention integer 900 undo_tablespace string SQL> alter system set undo_tablespace='UNDOTBS1' scope=spfile; System altered. SQL> startup force ORACLE instance started. Total System Global Area 1207959552 bytes Fixed Size 2095864 bytes Variable Size 369100040 bytes Database Buffers 822083584 bytes Redo Buffers 14680064 bytes Database mounted. Database opened. SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 SQL>