查询回滚段的信息
　　所用数据字典：DBA_ROLLBACK_SEGS

Column

Datatype

NULL

Description

SEGMENT_NAME

VARCHAR2(30)

NOT NULL

Name of the rollback segment

OWNER

VARCHAR2(6)

 

Owner of the rollback segment

TABLESPACE_NAME

VARCHAR2(30)

NOT NULL

Name of the tablespace containing the rollback segment

SEGMENT_ID

NUMBER

NOT NULL

ID number of the rollback segment

FILE_ID

NUMBER

NOT NULL

File identifier number of the file containing the segment head

BLOCK_ID

NUMBER

NOT NULL

ID number of the block containing the segment header

INITIAL_EXTENT

NUMBER

 

Initial extent size in bytes

NEXT_EXTENT

NUMBER

 

Secondary extent size in bytes

MIN_EXTENTS

NUMBER

NOT NULL

Minimum number of extents

MAX_EXTENTS

NUMBER

NOT NULL

Maximum number of extent

PCT_INCREASE

NUMBER

NOT NULL

Percent increase for extent size

STATUS

VARCHAR2(16)

 

Rollback segment status

INSTANCE_NUM

VARCHAR2(40)

 

Rollback segment owning Oracle Real Application Cluster instance number

RELATIVE_FNO

NUMBER

NOT NULL

Relative file number of the segment header


　　可以查询的信息：回滚段的标识(SEGMENT_ID)、名称(SEGMENT_NAME)、所在表空间(TABLESPACE_NAME)、类型(OWNER)、状态(STATUS)。
　　例：
　　SQL>SELECT segment_name,tablespace_name,owner,status FROM dba_rollback_segs;
回滚段的统计信息
　　数据字典：V$ROLLNAME,V$ROLLSTAT

Column

Datatype

Description

USN

NUMBER

Rollback segment number

LATCH

NUMBER

Latch for the rollback segment

EXTENTS

NUMBER

Number of extents in the rollback segment

RSSIZE

NUMBER

Size (in bytes) of the rollback segment. This value differs by the number of bytes in one database block from the value of the BYTES column of the ALL/DBA/USER_SEGMENTS views.

See Also: Oracle9i Database Administrator's Guide.

WRITES

NUMBER

Number of bytes written to the rollback segment

XACTS

NUMBER

Number of active transactions

GETS

NUMBER

Number of header gets

WAITS

NUMBER

Number of header waits

OPTSIZE

NUMBER

Optimal size of the rollback segment

HWMSIZE

NUMBER

High water mark of rollback segment size

SHRINKS

NUMBER

Number of times the size of a rollback segment decreases

WRAPS

NUMBER

Number of times rollback segment is wrapped

EXTENDS

NUMBER

Number of times rollback segment size is extended

AVESHRINK

NUMBER

Average shrink size

AVEACTIVE

NUMBER

Current size of active extents, averaged over time.

STATUS

VARCHAR2(15)

Rollback segment status:

  • ONLINE

  • PENDING OFFLINE

  • OFFLINE

  • FULL

CUREXT

NUMBER

Current extent

CURBLK

NUMBER

Current block

例：
　　SQL>SELECT n.name,s.extents,s.rssize,s.optsize,s.hwmsize,s.xacts,s.status
　　　　FROM v$rollname n,v$rollstat s
　　　　WHERE n.usn=s.usn;
 数据字典：v$undostat

V$undostat 动态字典给出一个系统工作的统计柱状图。统计内容包括花费空间，事务数量和实例的执行时间长度等信息。DBA可以使用这个视图中的统计信息估计需要的回滚段空间数量。如果回滚段是处于手工管理状态，则这个视图中没有数据。

系统将会间隔10分钟搜集一次统计信息并保存到动态视图v$undostat中，结果按照begin_time字段降序排列。视图最多保存1008条数据记录，保留7天的循环数据。

Column

Datatype

Description

BEGIN_TIME

DATE

Identifies the beginning of the time interval

END_TIME

DATE

Identifies the end of the time interval

UNDOTSN

NUMBER

Represents the last active undo tablespace in the duration of time. The tablespace ID of the active undo tablespace is returned in this column. If more than one undo tablespace was active in that period, the active undo tablespace that was active at the end of the period is reported.

UNDOBLKS

NUMBER

Represents the total number of undo blocks consumed. You can use this column to obtain the consumption rate of undo blocks, and thereby estimate the size of the undo tablespace needed to handle the workload on your system.

TXNCOUNT

NUMBER

Identifies the total number of transactions executed within the period

MAXQUERYLEN

NUMBER

Identifies the length of the longest query (in number of seconds) executed in the instance during the period. You can use this statistic to estimate the proper setting of the UNDO_RETENTION initialization parameter.

MAXCONCURRENCY

NUMBER

Identifies the highest number of transactions executed concurrently within the period

UNXPSTEALCNT

NUMBER

Number of attempts to obtain undo space by stealing unexpired extents from other transactions

UNXPBLKRELCNT

NUMBER

Number of unexpired blocks removed from certain undo segments so they can be used by other transactions

UNXPBLKREUCNT

NUMBER

Number of unexpired undo blocks reused by transactions

EXPSTEALCNT

NUMBER

Number of attempts to steal expired undo blocks from other undo segments

EXPBLKRELCNT

NUMBER

Number of expired undo blocks stolen from other undo segments

EXPBLKREUCNT

NUMBER

Number of expired undo blocks reused within the same undo segments

SSOLDERRCNT

NUMBER

Identifies the number of times the error ORA-01555 occurred. You can use this statistic to decide whether or not the UNDO_RETENTION initialization parameter is set properly given the size of the undo tablespace. Increasing the value of UNDO_RETENTION can reduce the occurrence of this error.

NOSPACEERRCNT

NUMBER

Identifies the number of times space was requested in the undo tablespace and there was no free space available. That is, all of the space in the undo tablespace was in use by active transactions. The corrective action is to add more space to the undo tablespace.

 

回滚段的当前活动事务
　　数据字典：V$SESSION,V$TRANSACTION
　　例：
　　SQL>SELECT s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk
　　　　FROM v$session s,v$transaction t
　　　　WHERE s.saddr=t.ses_addr;
　　 USERNAME　　XIDUSN　　 UBAFIL　　 UBABLK　　USED_UBLK
　　 -------　　-------- ----------- ----------- -----------
　　 SYSTEM　　　　　　2　　　　　　2　　　　 7　　　　　　1
　　 SCOTT　　　　　　 1　　　　　　2　　　　163　　　　　　1
　　 2 rows selected.

 

回滚段的数量规划
　　对于OLTP系统，存在大量的小事务处理，一般建议：
　　数量多的小回滚段；每四个事务一个回滚段；每个回滚段不要超过十个事务。
　　对于批处理，一般建议：

　　少的大回滚段；每个事务一个回滚段。

回滚段的问题及解决方法
　　问题一：事务要求的回滚段空间不够，表现为表空间用满（ORA-01560错误），回滚段扩展到达参数MAXEXTENTS的值（ORA-01628）。

　　解决方法：向回滚段表空间添加文件或使已有的文件变大；增加MAXEXTENTS的值。

　　问题二：读一致性错误（ORA-01555 SNAPSHOT TOO OLD）

　　解决方法：增加MINEXTENTS的值，增加区的大小，设置一个高的OPTIMAL值。