查询回滚段的信息
　　所用数据字典：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值。