今天新搭建了一个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