ORA-01562:无法扩展回滚段号 [英] ORA-01562: failed to extend rollback segment number

问题描述

我正在逐行读取文件，并且在每一行之后将数据提交给DB.输入文件包含100K行. 偶尔我会收到ORA-01562: failed to extend rollback segment number错误，它不是针对特定的行，也不是针对特定的文件.该错误是随机的.这是在生产系统上发生的.

I am reading a file row by row and I am committing the data to DB after every row. The input file contains 100K rows. Once in a while I am getting ORA-01562: failed to extend rollback segment number error and it is not for a particular row, nor for a particular file. The error is random. This is happening on production system.

并且该表的MAXEXTENTS已设置为UNLIMITED. 我无法在我的开发环境上重现该错误.

And the MAXEXTENTS for that table is already set to UNLIMITED. I am unable to reproduce the error on my development environment.

如何处理这种情况?我还需要采取什么其他步骤才能使以后不再出现此错误?

How to handle this scenario? What other steps I need to take so that I wont get this error in the future?

推荐答案

您需要做的是shrink回滚段或add另一个回滚段.

What you need to do is to shrink the rollback segment or add another rollback segment.

在您的情况下，您似乎只有one个回滚段，即默认的SYSTEM回滚段.

In your case it looks like you have only one rollback segment, the default SYSTEM rollback segment.

您必须再添加至少一个回滚段.

You must add atleast one more rollback segment.

以下是对您有帮助的命令

Here are the commands which will help you

以SYSTEM用户身份登录时发出这些语句.

issue these statements while logged in as SYSTEM user.

手动缩小回滚段:

要使用收缩回滚段，您必须具有ALTER ROLLBACK SEGMENT系统特权.

To shrink a rollback segment using you must have the ALTER ROLLBACK SEGMENT system privilege.

您可以使用SQL命令ALTER ROLLBACK SEGMENT手动减小回滚段的大小.您尝试缩小的回滚段必须在线.

You can manually decrease the size of a rollback segment using the SQL command ALTER ROLLBACK SEGMENT. The rollback segment you are trying shrink must be online.

以下语句将回滚段RBS1缩小到100K:

The following statement shrinks rollback segment RBS1 to 100K:

ALTER ROLLBACK SEGMENT rbs1 SHRINK TO 100K;

显示回滚段信息:

DBA_ROLLBACK_SEGS数据字典视图存储有关数据库回滚段的信息.例如，以下查询列出了数据库中每个回滚段的名称，关联的表空间和状态:

The DBA_ROLLBACK_SEGS data dictionary view stores information about the rollback segments of a database. For example, the following query lists the name, associated tablespace, and status of each rollback segment in a database:

SELECT segment_name, tablespace_name, status
         FROM sys.dba_rollback_segs;

输出

SEGMENT_NAME  TABLESPACE_NAME     STATUS
 ------------- ----------------    ------
SYSTEM        SYSTEM             ONLINE
 PUBLIC_RS     SYSTEM             ONLINE
 USERS_RS      USERS              ONLINE

创建回滚段:

要创建回退段，您必须具有CREATE ROLLBACK SEGMENT系统特权.要为数据库创建其他回滚段，请使用企业管理器的创建回滚段"属性表或SQL命令CREATE ROLLBACK SEGMENT.包含新回滚段的表空间必须在线.

To create rollback segments, you must have the CREATE ROLLBACK SEGMENT system privilege. To create additional rollback segments for a database, use either the Create Rollback Segment property sheet of Enterprise Manager, or the SQL command CREATE ROLLBACK SEGMENT. The tablespace to contain the new rollback segment must be online.

以下语句使用USERS表空间的默认存储参数在USERS表空间中创建一个名为USERS_RS的公共回滚段:

The following statement creates a public rollback segment named USERS_RS in the USERS tablespace, using the default storage parameters of the USERS tablespace:

CREATE PUBLIC ROLLBACK SEGMENT users_rs TABLESPACE users;

这篇关于ORA-01562:无法扩展回滚段号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！