建议看看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;
10g使用撤销段。回滚段不再推荐使用,所以默认rollback_segments为空,而undo_management=auto undo_retention是撤销保留时间,默认就为900秒。也就是过期数据为了一致性查询再保留900秒。值大小是否合适?撤销表空间要多大?这主要看你最大的查询耗时多久。 可以通过EM查看撤销管理顾问程序来设置,或者手工计算: select (select max(undoblks)/600*max(maxquerylen) from v$undostat) * (select value from v$parameter where name='db_block_size') from dual; 这个查询可以估算撤销表空间的大小(字节)。不过用这个查询时数据库要已经运行了较长时间,而最耗时的查询已经运行过了。 它就是用最长查询时间乘这期间生成的撤销块。
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_management 为自动管理。可以通过设置较大的undo表空间和undo_retention值来避免ora-01555;
同意oracledbalgtu的观点,先从应用上找找毛病,不要总是从调整oracle的初始化配置想办法。
一次较大量数据的操作,在一个DB上 报错了
而同样的数据处理 在另一个db上 就没问题
所以 我只想改 DB设置
rollback_segments 我的DB上是 默认的 NULL谢谢 几位再问下 undo_retention 现在是900
undo表空间 495M
如果要处理 几十万的数据 以上值大小 是否合适?
undo_retention是撤销保留时间,默认就为900秒。也就是过期数据为了一致性查询再保留900秒。值大小是否合适?撤销表空间要多大?这主要看你最大的查询耗时多久。
可以通过EM查看撤销管理顾问程序来设置,或者手工计算:
select
(select max(undoblks)/600*max(maxquerylen) from v$undostat)
*
(select value from v$parameter where name='db_block_size')
from dual;
这个查询可以估算撤销表空间的大小(字节)。不过用这个查询时数据库要已经运行了较长时间,而最耗时的查询已经运行过了。
它就是用最长查询时间乘这期间生成的撤销块。