今天新搭建了一个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
分类： 技术博文 2012-07-10 06:23 232人阅读 评论(0) 收藏 举报
ORA-01552: cannot use system rollback segment for...错误解决
故障现象：
今天本要做数据库的不完全恢复，但是在准备环境的时候需要创建几张表做为恢复用的基表。在创建表的时候
就报了如下一段错误：
13:56:03 SQL> create table wwl001 (id number,name varchar(12));
create table wwl001 (id number,name varchar(12))
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'WWL'；
!!!! 非系统表空间'WWL'不能使用系统回滚段
问题原因：
我昨天做了由于磁盘损坏导致数据库灾难的恢复，可能是由于恢复的时候参数文件修改了，所以才导致成回滚段
变成了手动管理模式，我们可以看看如下步骤：
详情：
１、创建表，非系统表空间不能使用回滚段
13:56:03 SQL> create table wwl001 (id number,name varchar(12));
create table wwl001 (id number,name varchar(12))
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'WWL'；
2、查看回滚段管理方式，为手动管理
13:57:24 SQL> show parameter undo
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management
string MANUAL
undo_retention
integer 900
undo_tablespace
string UNDOTBS1
3、查看回滚段状态
13:57:23 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
11 rows selected.
4、针对现象修改undo_management参数为auto即可。
14:08:06 SQL> alter system set undo_management=auto scope=spfile;
System altered.
14:17:51 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
14:18:22 SQL> startup
ORACLE instance started.
Total System Global Area 100663296 bytes
Fixed
Size
1217884 bytes
Variable
Size
88083108 bytes
Database
Buffers
8388608 bytes
Redo
Buffers
2973696 bytes
Database mounted.
Database opened.
5、查看回滚段状态
14:18:55 SQL> select segment_name, tablespace_name, status from
dba_rollback_segs;
SEGMENT_NAME
TABLESPACE_NAME
STATUS
------------------------------ ------------------------------ ----------------
SYSTEM
SYSTEM
ONLINE
_SYSSMU1$
UNDOTBS1
ONLINE
_SYSSMU2$
UNDOTBS1
ONLINE
_SYSSMU3$
UNDOTBS1
ONLINE
_SYSSMU4$
UNDOTBS1
ONLINE
_SYSSMU5$
UNDOTBS1
ONLINE
_SYSSMU6$
UNDOTBS1
ONLINE
_SYSSMU7$
UNDOTBS1
ONLINE
_SYSSMU8$
UNDOTBS1
ONLINE
_SYSSMU9$
UNDOTBS1
ONLINE
_SYSSMU10$
UNDOTBS1
ONLINE
11 rows selected.
6、我们再执行刚才的建表语句，非常好，可以创建表了。
14:19:51 SQL> create table wwl001 (id number,name varchar(12));
Table created
SQL> alter system set undo_management=auto scope=spfile;
系统已更改。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup;
ORACLE 例程已经启动。
数据库装载完毕。
数据库已经打开。
SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
表空间已删除。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup;
ORACLE 例程已经启动。
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 167775108 bytes
Database Buffers 436207616 bytes
Redo Buffers 7135232 bytes
数据库装载完毕。
数据库已经打开。
分类： 数据库相关 2009-04-09 13:52 1444人阅读 评论(0) 收藏 举报
1. 概述
本文主要从回滚段的原理，分配和使用，以及回滚段的相关参数包括初始化参数的设置和回滚段的管理来介绍回滚段。以及回滚段涉及的多种问题。
2. 回滚段工作原理
回滚段是磁盘上的一段存储空间，用来保存数据变化前后的映像来提供一致性读，保证事务完整性。
oracle 基于两个原则给事务分配回滚段。首先oracle试图将一个新的事务指派给某个拥有最少活动事务数的回滚段；如果没有单个段能满足这个需求，那么该事务将
被指派给某个段来保存undo信息，以便此undo信息能够尽可能长的时间内被用于读一致性视图保留。读一致性保证查询或者返回数据，或者失败。当失败时
系统发出ora-01555错误。所以，回滚段中已经提交的事务数据是非常宝贵的。Oracle设法尽可能长的保留回滚段已经提交的数据。
回滚段 中的范围以循环的方式被使用。一个事务仅仅使用一个回滚段。当一个范围装满回滚段数据后回滚段就自动使用下一个范围。多个事务可以使用一个范围，但是一个
回滚段的数据块只能存放一个事务数据。当回滚段使用完最后一个范围时，当回滚段的第一个范围是不活动的或者不被使用后，回滚段的指针才指向第一个范围。
3. 回滚段的类型
回滚段分系统回滚段和非系统回滚段，其中非系统回滚段又分为PUBLIC回滚段和PRIVATE回滚段.
系统回滚段用于处理涉及系统的CATALOG的事物(比如大多数的DDL), 它位于SYSTEM表空间, 由于只有SYSTEM表空间可以随时保持可用, 因此, 不要把SYSTEM回滚段放在其他的表空间中.
注意：系统回滚段应放在SYSTEM表空间中, 并且应该永远保持ONLINE状态.
PUBLIC回滚段对于数据库的所有实例(INSTANCE)都是可用的, 除非将其设置为OFFLINE。创建后只要online就可用。
PRIVATE 回滚段是指对于数据库的某个实例是私有的, 为了使用PRIVATE回滚段, 某个实例应当在其INITsid.ORA的 ROLLBACK_SEGMENTS中标明所有要使用的PRIVATE回滚段, 或通过使用 ALTER ROLLBACK SEGMENT XXX ONLINE来使用某一个回滚段.
注意：在单实例系统中,建议将所有回滚段设为PUBLIC；在多实例系统中(如OPS), 建议将每个实例的PRIVATE回滚段放置到访问比较快的本地设备上。
4. 回滚段的使用
一般情况下，如果对回滚段的大小估计不准确的话，会导致系统产生回滚段不能自动扩展范围的错误。
4.1. 创建回滚段
当创建回滚段时，系统必须要有CREATE ROLLBACK SEGMENT系统权限。
CREATE ROLLBACK SEGMENT RB01 TABLESPACE RBS1
STORAGE (INITIAL integer K|M NEXT integer K|M MINEXTENTS integer MAXEXTENTS integer OPTIMAL integer K|M );
4.2. 更改ONLINE/OFFLINE状态
ALTER ROLLBACK SEGMENT RB01 ONLINE;
4.3. 更改OPTIMAL参数
ALTER ROLLBACK SEGMENT RB01 STORAGE ( MAXEXTENTS 200 OPTIMAL 2048K );
4.4. 缩小回滚段
ALTER ROLLBACK SEGMENT RB01 SHRINK;
(有OPTIMAL参数时, 缩小到OPTIMAL值; 没有OPTIMAL参数时, 缩小到MINEXTENTS所对应的尺寸) ALTER ROLLBACK SEGMENT RB01 SHRINK TO 2048K;
4.5. 修改INITIAL/NEXT参数
原则上，INITIAL总应该等于NEXT，除了使用SET TRANSACTION USE ROLLBACK SEGMENT XXX的回滚段。由于INITIAL不能直接修改，只能先drop然后创建。
DROP ROLLBACK SEGMENT RB01;
CREATE ROLLBACK SEGMENT RB01 TABLESPACE RBS1
STORAGE ( INITIAL 100K NEXT 100K MINEXTENTS 20 MAXEXTENTS 121 OPTIMAL 2000K);
4.6. 在事务中使用特定的回滚段
SET TRANSACTION USE ROLLBACK SEGMENT RB_LARGE1;
4.7. 删除回滚段
DROP ROLLBACK SEGMENT rollback_segment;
5. 重作表空间的使用
在oracle9i中推荐使用重做表空间代替回滚段，当然也可是使用回滚段。重做表空间是一种让系统来自动管理回滚段的方式。在系统里有4个和重做表空间的相关参数：
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- --------
undo_management string AUTO
undo_retention integer 900
undo_suppress_errors boolean TRUE
undo_tablespace string rbs3
SQL>
当 将undo_management设置成AUTO时系统使用重做表空间来管理回滚段，当它被设置成MENUAL时系统使用回滚段。 Undo_tablespace指名系统使用哪一个重做表空间。undo_suppress_errors被设置成true时表示系统创建和使用回滚段时 忽略错误。undo_retention是自动管理模式下系统提交后，回滚段的数据保留多长时间，单位是秒。这个参数的设置要看保留的时间和硬盘的空间大
小来设定。下面是Undo 表空间大小的设计规范的计算公式：
Undospace = UR * UPS *db_block_size+ 冗余量
UR： 表示在undo中保持的最长时间数（秒），由数据库参数UNDO_RETENTION值决定。
UPS：表示在undo中，每秒产生的数据库块数量。
例如：在数据库中保留2小时的回退数据，假定每秒产生200个数据库块。则Undospace = 2 * 3600 * 200 * 4K = 5.8G
5.1. 创建重做表空间
CREATE UNDO TABLESPACE "RBS2"
DATAFILE '$DATAHOME/RBS2_1.dbf' SIZE 200M REUSE AUTOEXTEND ON
NEXT 1024K MAXSIZE 600M;
5.2. 增加数据文件
ALTER TABLESPACE RBS3 ADD DATAFILE '/export/home2/oradata/openview/rbs3_3.dbf' SIZE 10M AUTOEXTEND ON NEXT 5M MAXSIZE 300M;
5.3. 修改数据文件
增加数据文件的大小：
ALTER DATABASE DATAFILE '/export/home2/oradata/openview/RBS3_2.dbf' RESIZE 300M;
修改数据文件的扩展类参数
ALTER DATABASE DATAFILE '/export/home2/oradata/openview/RBS3_2.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE 500M;
6. ORA-01555问题
这 个问题是一致性读取时发生的。回滚段是以循环方式使用回滚段中的范围，当前范围被写满后就使用下一个范围。当回滚段的撤销数据不再是活动的，可能是已经提
交或者回滚，那么空间就可以重用。当一个长时间运行的查询在事务活动时开始运行，当事务结束时还没有运行完，此时该查询意识到旧数据已经不复存在时，就产 生了ORA-01555:snapshot too old的错误。
从数据库应用的角度来讲，产生这个错误的原因有：
大的查询。一般来说，需要DBA找到这个大的查询，如果可以优化的话，尽量提高性能，否则为大的查询所使用的事务建立单独的回滚段，当查询开始时将该段联机，完成后再将该段脱机；或者象oracle建议那样使用更大的回滚段。
过度频繁的提交。可以将单条提交的事务改成成批提交。
或者由于设置了较小的optimal参数。此时可以将这个参数加大。
很显然，回滚段为数据库的管理提供一个挑战的机会。不过在oracle9i中使用自动管理undo表空间来解决回滚段的管理问题，因此这个错误成为了历史。
7. ORA-01552 错误
ORA-01552 cannot use system rollback segment for non-system tablespace。
这个错误说明在系统中没有可用的非系统回滚段。原因如下：
除了系统回滚段, 未创建其它回滚段Ø
只创建了PRIVATE回滚段, 但INITsid.ORA的ROLLBACK_SEGMENTS中未列出这些回滚段Ø
创建了PUBLIC回滚段, 但这些回滚段都处于OFFLINE状态Ø
解决办法，根据上述原因依次解决。
8. 创建回滚段时需要注意的问题
INITIAL和 NEXT最好一样，除了应用使用指定的回滚段。Ø
Private回滚段如果需要被其他用户使用，那么需要在INITsid.ora中使用rollback_segments指定。Ø
MINEXTENTS最小等于2Ø
OPTIMAL最小应该设置成两个extents大小。Ø
9. 与之相关的性能视图
和undo有关的动态性能视图
v$undostat 包含undo的统计信息。使用这张视图可以估计系统当前所需的undo大小。²
v$rollstat undo模式的视图。是undo表空间的undo segments的 统计信息²
v$transaction 包含undo segments的信息。²
dba_undo_extents 包含undo表空间中每一个范围的提交时间。²
和回滚段相关的性能视图
DBA_ROLLBACK_GEGS 描述回滚段的信息，包含回滚段的名字和表空间；²
DBA_SEGMENTS 描述回滚段的附加信息；²
V$ROLLNAME 列出在线回滚段的名称²
V$ROLLSTAT 包含回滚段的统计信息²
V$TRANSACTION 包含撤销的统计信息²