遭遇ORA-01552错误

遭遇ORA-01552错误
2008-09-25 14:59 1174人阅读 评论(1) 收藏 举报

今天新搭建了一个10g的测试数据库，运行都很正常，但是在打开autotrace功能后执行语句，报错

SQL> set autotrace on
SQL> select username,sid,serial#,server,paddr,status from v$session where username=USER;

USERNAME SID SERIAL# SERVER PADDR STATUS

---

SYS 35 11 DEDICATED 2553CCE8 ACTIVE
SYS 50 16 DEDICATED 2553CCE8 INACTIVE

Execution Plan

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01552: cannot use system rollback segment for non-system tablespace ‘TEMPTS1’

SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics

      0  recursive calls
      0  db block gets
      0  consistent gets
      0  physical reads
      0  redo size
    768  bytes sent via SQL*Net to client
    380  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      2  rows processed

错误提示非系统表空间TEMPTS1不能使用系统回退段，查看回滚段管理模式和回滚表空间。

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$UNDOTBS OFFLINE _SYSSMU2$ UNDOTBS OFFLINE
_SYSSMU3 UNDOTBSOFFLINESYSSM