日期:2014-05-17  浏览次数:20817 次

关于ORA-01555 rollback_segments 请教 解决立即 送分
ORA-01555 rollback_segments 设置 过小?应该设置多大合适?
TYPE好像是string
DEFAULT NULL 时使用的是 PUBLIC rollback_segments 这个指的是什么?

------解决方案--------------------
建议看看note:3807 Note 3807 - Error messages regarding rollback and undo segments

可以试着按下面的例子,修改Rollback segments的参数。

When classic rollback segments are used, the essential factor causing an ORA-01555 is an insufficient guaranteed minimum size of the rollback segments which can be influenced by the memory parameters of the rollback segments. 
The following memory parameters are important in this process: 
a) INITIAL: Size of the first extent
b) NEXT: Size of all other extents
c) MINEXTENTS: Initial number of allocated extents
d) MAXEXTENTS: Maximum number of allocated extents
e) OPTIMAL: Size to which a rollback segment is reduced, if it increased in the meantime.
The guaranteed minimum size of a rollback segment is determined by multiplying memory parameters NEXT and MINEXTENTS. In addition, memory parameter OPTIMAL must not be smaller than this value. In many cases, the values are below 10 MB. For many applications, this is not enough, so the danger of an ORA-01555 increases. Depending on the size of the system and transactions, guaranteed minimum sizes from 20 MB to several 100 MB are required. In general, an appropriate value can only be determined through trial-and-error. The number of rollback segments and the size of the rollback tablespace are less important when trying to avoid error ORA-01555. For more information,see to Notes 60233 and 185822. For an optimum configuration of the rollback segments, observe the following rules of thumb: 
INITIAL = NEXT
MINEXTENTS >= 10
NEXT * MINEXTENTS * No. of rollback segments * 1.25 = Size of PSAPROLL
OPTIMAL = NEXT * MINEXTENTS
MAXEXTENTS >= 0.2 * Size of PSAPROLL / NEXT + MINEXTENTS
Below are two examples for an appropriate configuration. The first example describes a permanent setting of the rollback segments, while the second should only be implemented in exceptional cases (for example, temporarily during a client copy). If error ORA-01555 still occurs despite the approaches described here, check whether the problem may be solved on the application. If this is not the case, create an SAP message. Provide SAP with the following information: 
Size of PSAPROLL
Number of rollback segments
Memory parameters: INITIAL, NEXT, MINEXTENTS, MAXEXTENTS, OPTIMAL
Example 1: 20 Rollback segments, PSAPROLL: 2.5 GB 

INITIAL 10M
NEXT 10M
MINEXTENTS 10
MAXEXTENTS 60
OPTIMAL 100M
In this example, each rollback segment has a guaranteed minimum size of 100M (NEXT * MIN_EXTENTS). This means that 80 % of PSAPROLL is filled. (100M * 20 = 2 GB). The remaining 20 % remain available for any long-running transactions but are released again later due to OPTIMAL = 100M. To change the storage parameters, you need to delete and recreate the rollback segments. To do this, use the following commands:

ALTER ROLLBACK SEGMENT PRS_<num> OFFLINE;
DROP ROLLBACK SEGMENT PRS_<num>;
CREATE ROLLBACK SEGMENT PRS_<num> TABLESPACE PSAPROLL
STORAGE (INITIAL <new_initial>
NEXT <new_next>
MINEXTENTS <new_minextents>
MAXEXTENTS <new_maxextents>
OPTIMAL <new_optimal>);
ALTER ROLLBACK SEGMENT PRS_<num> ONLINE;
------解决方案--------------------
出现这样的问题,经验是从应用上修改,让执行时间长的业务,减少总时间或者修改业务操作集中做。
而不是从数据库上修改。
如果数据库undo设置的确存在问题的话,可以如下操作:
目前数据库,一般使用undo_man