遭遇ORA-01552错误

今天新搭建了一个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$                      UNDOTBS                        OFFLINE
_SYSSMU4$                      UNDOTBS                        OFFLINE
_SYSSMU5$                      UNDOTBS                        OFFLINE
_SYSSMU6$                      UNDOTBS                        OFFLINE
_SYSSMU7$                      UNDOTBS                        OFFLINE
_SYSSMU8$                      UNDOTBS                        OFFLINE
_SYSSMU9$                      UNDOTBS                        OFFLINE
_SYSSMU10$                     UNDOTBS                        OFFLINE

因为这库是手工创建的，在编辑初始化参数文件时，忘了设置undo_management和undo_tablespace。知道了这个，我想解决起来也就很简单了，

首选的当然是使用自动管理表空间的方式。

SQL> alter system set undo_tablespace=UNDOTBS scope=spfile;

System altered.

SQL> alter system set undo_management=auto scope=spfile;

System altered.

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

Total System Global Area   96468992 bytes
Fixed Size                  1217884 bytes
Variable Size              88083108 bytes
Database Buffers            4194304 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

另外，我们也可以继续沿用老的手动管理回滚段的方式。

SQL> create rollback segment rbs01;

Rollback segment created.

SQL> alter rollback segment rbs01 online;

Rollback segment altered.

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$                      UNDOTBS                        OFFLINE
_SYSSMU4$                      UNDOTBS                        OFFLINE
_SYSSMU5$                      UNDOTBS                        OFFLINE
_SYSSMU6$                      UNDOTBS                        OFFLINE
_SYSSMU7$                      UNDOTBS                        OFFLINE
_SYSSMU8$                      UNDOTBS                        OFFLINE
_SYSSMU9$                      UNDOTBS                        OFFLINE
_SYSSMU10$                     UNDOTBS                        OFFLINE

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
RBS01                          SYSTEM                         ONLINE

这样，就不会有任何问题了。

SQL> set autotrace trace
SQL> set line 124
SQL> select tablespace_name from dba_tablespaces;


Execution Plan
----------------------------------------------------------
Plan hash value: 3778488125

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     5 |    65 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TS$  |     5 |    65 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TS"."ONLINE$"<>3 AND BITAND("FLAGS",2048)<>2048)


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