出现ORA-01552: cannot use system rollback segment

问题：
1 SQL> insert into test values(3);
insert into test values(3)
            *
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'



2  看alert_ORCL.log日志：
Thu May  8 19:16:04 2008
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Thu May  8 19:16:05 2008
Successfully onlined Undo Tablespace 1.
Thu May  8 19:16:05 2008
SMON: enabling tx recovery
Thu May  8 19:16:05 2008
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:

         ALTER TABLESPACE ADD TEMPFILE

         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is WE8ISO8859P1
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=21, OS id=8211
Thu May  8 19:16:11 2008
Completed: ALTER DATABASE OPEN




3 查回滚段表
SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                         ONLINE
_SYSSMU1$                      NEEDS RECOVERY
_SYSSMU2$                      NEEDS RECOVERY
_SYSSMU3$                      NEEDS RECOVERY
_SYSSMU4$                      NEEDS RECOVERY
_SYSSMU5$                      NEEDS RECOVERY
_SYSSMU6$                      NEEDS RECOVERY
_SYSSMU7$                      NEEDS RECOVERY
_SYSSMU8$                      NEEDS RECOVERY
_SYSSMU9$                      NEEDS RECOVERY
_SYSSMU10$                     NEEDS RECOVERY

11 rows selected.

4 SQL> select us#,name,status$ from undo$;

       US# NAME                              STATUS$
---------- ------------------------------ ----------
         0 SYSTEM                                  3
         1 _SYSSMU1$                               5
         2 _SYSSMU2$                               5
         3 _SYSSMU3$                               5
         4 _SYSSMU4$                               5
         5 _SYSSMU5$                               5
         6 _SYSSMU6$                               5
         7 _SYSSMU7$                               5
         8 _SYSSMU8$                               5
         9 _SYSSMU9$                               5
        10 _SYSSMU10$                              5

       US# NAME                              STATUS$
---------- ------------------------------ ----------
        11 _SYSSMU11$                              1
        12 _SYSSMU12$                              1
        13 _SYSSMU13$                              1
        14 _SYSSMU14$                              1
        15 _SYSSMU15$                              1
        16 _SYSSMU16$                              1
        17 _SYSSMU17$                              1
        18 _SYSSMU18$                              1
        19 _SYSSMU19$                              1
        20 _SYSSMU20$                              1

21 rows selected.

状态为5的有10行，对应上面的dba_rollback_segs表
但是这里状态是需要恢复


查看数据库启动是用spfile还是pfile文件
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
如果为空则是从pfile启动，有值就是从spfile启动的！

6   编辑pfile文件：/u01/app/oracle/product/10.2.0/db_1/dbs/initORCL.ora

将*.undo_tablespace='UNDOTBS1'改成*.undo_tablespace='UNDOTBS2'

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  268435456 bytes
Fixed Size                  1218868 bytes
Variable Size              88082124 bytes
Database Buffers          171966464 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2



7   然后把整个系统重启一下就没出现问题了。

默认表空间没有空间了，可以切换回滚空间untotbs2为系统的默认表空间。（也可以增加默认表空间untotbs1的大小。）

 

 

2008/05/09

1 目标数据库控制文件在某个极大日期范围内存储值得保留的备份与归档日志信息，由

control_file_record_keep_time参数确定。默认为7天，即在第8天时，最陈旧的信息（从第一天开始

）可以被覆盖。

SQL> show parameter control_file_record_keep_time

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7

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

转载于:http://blog.itpub.net/12778571/viewspace-263382/