oracle回滚段的一些常用sql

*******************************************

关Undo表空间与回滚段的一些查询语句记录

--与回滚段相关的几个系统参数
transactions_per_rollback_segment
transactions
max_rollback_segments
rollback_segments   回滚段类型为public则与该参数无关
--相关的几个视图:
DBA_UNDO_EXTENTS
GV$UNDOSTAT
V$UNDOSTAT
DBA_ROLLBACK_SEGS
GV$ROLLSTAT
V$ROLLNAME
V$ROLLSTAT
---分析 UNDO 的使用情况
SELECT TABLESPACE_NAME,STATUS,TRUNC(SUM(BLOCKS) * 8 / 1024) AS "Size M",
COUNT(*) Undo_Extent_Num
FROM DBA_UNDO_EXTENTS
GROUP BY TABLESPACE_NAME, STATUS

---监控undo表空间
SELECT BEGIN_TIME, END_TIME, UNDOTSN, UNDOBLKS, TXNCOUNT,
        MAXCONCURRENCY AS "MAXCON"
     FROM V$UNDOSTAT;
--查询是否有回滚段的争用
select * from v$waitstat;

SELECT name, waits, gets, waits/gets "Ratio"
FROM v$rollstat a, v$rollname b
WHERE a.usn = b.usn;
---查看回滚段的统计信息：
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;
--查看回滚段的使用情况，哪个用户正在使用回滚段的资源:
SELECT s.username, u.name
FROM v$transaction t, v$rollstat r, v$rollname u, v$session s
WHERE s.taddr = t.addr
   AND t.xidusn = r.usn
   AND r.usn = u.usn
ORDER BY s.username;
--查询回滚段的事务回退率
transaction rollbacks/(transaction rollbacks+user commits)
select name,value from v$syssstat where name in('user commits','transaction rollbacks');
--查询获取回滚段数据的时候数据缓冲区中copy的数据块的数量
select count(*) from x$bh where state=3;
--查询在SGA中回滚段的块的数量USN=n,则回滚段头class为11+2n,回滚段块为12+2n
select usn from v$rollstat;
select class,count(*) from x$bh where class>10 group by class;
--查询数据库的的回滚段情况
select segment_id,segment_name from dba_rollback_segs;
--指定使用某个回滚段
set transaction use rollback segment _SYSSMU4$
--查询回滚段在使用,扩展,回缩的时候extent在循环的次数
select usn,wraps from v$rollstat;
--查询回滚段收缩的情况
select usn,optsize,shrinks from v$rollstat;
--切换undo表空间到新的表空间(注意修改pfile或者spfile参数)
alter system set undo_tablespace=UNDOTBS1 scope=both;
---创建undo表空间
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'E:\ORACLE\ORA92\ORCL9\UNDOTBS02.DBF' size 11M reuse AUTOEXTEND ON;
---改变(Altering) an Undo Tablespace
Adding a datafile
Renaming a datafile
Bringing a datafile online or taking it offline
Beginning or ending an open backup on a datafile
---增加数据文件
ALTER TABLESPACE UNDOTBS2
     ADD DATAFILE 'E:\ORACLE\ORA92\ORCL9\UNDOTBS03.DBF' size 2M AUTOEXTEND ON NEXT 1M
         MAXSIZE UNLIMITED;
--drop undo表空间
DROP TABLESPACE UNDOTBS2; ---INCLUDING CONTENTS.
--不指定undo表空间
ALTER SYSTEM SET UNDO_TABLESPACE = '';
--设置retention值:
ALTER SYSTEM SET UNDO_RETENTION = 5;
---计算undo表空间的大小(计算机数据可以从V$UNDOSTAT 得到)
UndoSpace = UR * UPS + overhead
UndoSpace is the number of undo blocks
UR is UNDO_RETENTION in seconds
UPS is undo blocks for each second
overhead is the small overhead for metadata (transaction tables, bitmaps, and so forth)
例如:UNDO_RETENTION 2 hours, transaction rate (UPS) 200 undo blocks for each second,
with a 4K block size则undo空间为:
(2 * 3600 * 200 * 4K) = 5.8GBs.
---使用Oracle提供的报表查看锁的情况:
@$ORACLE_HOME\rdbms\admin\utllockt.sql
---查询当前某个session的事务所使用的回滚段大小
select b.sid,a.xidusn,a.used_ublk from v$transaction a,v$session b where a.addr=b.taddr;
---如果要介质恢复一个数据文件,先offline再恢复(在归档模式)
alter database datafile 'E:\ORACLE\ORA92\ORCL9\UNDOTBS03.DBF' offline
recover datafile 'E:\ORACLE\ORA92\ORCL9\UNDOTBS03.DBF'
--
-----查看Undo的大小
set line 1000;
set pages 1000;
SELECT d.status "Status",
       d.tablespace_name "Name",
       d.contents "Type",
       d.extent_management "Extent Management",
       to_char(nvl(a.bytes / 1024 / 1024, 0), '99999999.999') "Total Size (M)",
       to_char(nvl(a.bytes - nvl(f.bytes, 0), 0) / 1024 / 1024,
               '99999999.999') "Used (M)",
       to_char(nvl(nvl(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') "Free (M)",
       to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0),
               '990.00') "Used %"
FROM sys.dba_tablespaces d,
       (SELECT tablespace_name, SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) bytes
          FROM dba_free_space
         GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = f.tablespace_name(+)
   AND NOT
        (d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY')
UNION ALL
SELECT d.status "Status",
       d.tablespace_name "Name",
       d.contents "Type",
       d.extent_management "Extent Management",
       to_char(nvl(a.bytes / 1024 / 1024, 0), '99999990.900') "Size (M)",
       to_char(nvl(t.bytes, 0) / 1024 / 1024, '99999999.999') "Used (M)",
       to_char((nvl(a.bytes / 1024 / 1024, 0)) -
               (nvl(t.bytes, 0) / 1024 / 1024),
               '99999999.999') "Free (M)",
       to_char(nvl(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d,
       (SELECT tablespace_name, SUM(bytes) bytes
          FROM dba_temp_files
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes_cached) bytes
          FROM v$temp_extent_pool
         GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = t.tablespace_name(+)
   AND d.extent_management LIKE 'LOCAL'
   AND d.contents LIKE 'TEMPORARY'
ORDER BY "Used %" DESC回滚段概述 

　　回滚段用于存放数据修改之前的值（包括数据修改之前的位置和值）。回滚段的头部包含正在使用的该回滚段事务的信息。一个事务只能使用一个回滚段来存放它的回滚信息，而一个回滚段可以存放多个事务的回滚信息。 

回滚段的作用 

　　事务回滚：当事务修改表中数据的时候，该数据修改前的值（即前影像）会存放在回滚段中，当用户回滚事务（ROLLBACK）时，ORACLE将会利用回滚段中的数据前影像来将修改的到原来的值。 

　　事务恢复：当事务正在处理的时候，例程失败，回滚段的信息保存在重做日志文件中，ORACLE将在下次打开数据库时利用回滚来恢复未提交的数据。 

　　读一致性：当一个会话正在修改数据时，其他的会话将看不到该会话未提交的修改。而且，当一个语句正在执行时，该语句将看不到从该语句开始执行后的未提交的修改（语句级读一致性）。当ORACLE执行SELECT语句时，ORACLE依照当前的系统改变号（SYSTEM CHANGE NUMBER-SCN）来保证任何前于当前SCN的未提交的改变不被该语句处理。可以想象：当一个长时间的查询正在执行时，若其他会话改变了该查询要查询的某个数据块，ORACLE将利用回滚段的数据前影像来构造一个读一致性视图。 

 

回滚段的类型

 

回滚段可分为系统回滚段和非系统回滚段, 其中非系统回滚段又分为PUBLIC回滚段和PRIVATE回滚段。

 

回滚段：1 系统回滚段

        2 非系统回滚段：(1)PUBLIC回滚段

                        (2)PRIVATE回滚段

 

系统回滚段用于处理涉及系统的CATALOG的事物(比如大多数的DDL), 它位于SYSTEM表空间, 由于只有SYSTEM表空间可以随时保持可用, 因此,不要把SYSTEM回滚段放在其他的表空间中.

 

原则1: 系统回滚段应放在SYSTEM表空间中, 并且应该永远保持ONLINE状态.

 

PUBLIC回滚段对于数据库的所有实例(INSTANCE)都是可用的, 除非将其显式设置为OFFLINE.

 

PRIVATE回滚段是指对于数据库的某个实例是私有的, 为了使用PRIVATE回滚段, 某个实例应当在其INITsid.ORA的ROLLBACK_SEGMENTS中标明所有要使用的PRIVATE回滚段, 或通过使用ALTER ROLLBACK SEGMENT XXX ONLINE来使用某一个回滚段.

 

建议1: 在单实例系统中,建议将所有回滚段设为PUBLIC.

 

建议2: 在多实例系统中(如OPS,RAC), 建议将每个实例的PRIVATE回滚段放置到访问比较快的本地设备上.

 

回滚段的数量规划 

　　对于OLTP系统，存在大量的小事务处理，一般建议： 

　　数量多的小回滚段；每四个事务一个回滚段；每个回滚段不要超过十个事务。 

　　对于批处理，一般建议： 

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

 

回滚段的使用

 

　　分配回滚段：当事务开始时，ORACLE将为该事务分配回滚段，并将拥有最少事务的回滚段分配给该事务。事务可以用以下语句申请指定的回滚段：

　　SET TRANSTRACTION USE ROLLBACK SEGMENT rollback_segment

　　事务将以顺序，循环的方式使用回滚段的区（EXTENTS），当当前区用满后移到下一个区。几个事务可以写在回滚段的同一个区，但每个回滚段的块只能包含一个事务的信息。

　　例如（两个事务使用同一个回滚段，该回滚段有四个区）：

　　1、事务在进行中，它们正在使用回滚段的第三个区；

　　2、当两个事务产生更多的回滚信息，它们将继续使用第三个区；

　　3、当第三个区满后，事务将写到第四个区，当事务开始写到一个新的区时，称为翻转（WRAP）；

4、当第四个区用满时，如果第一个区是空闲或非活动（使用该区的所有事务完成而没有活动的事务）的，事务将接着使用第一个区。

 

回滚段的查询

1 查询数据库的的回滚段情况

select owner,segment_id,segment_name,tablespace_name,status from dba_rollback_segs;

2 查看系统回滚段基本信息

select segment_name,tablespace_name,bytes,next_extent from dba_segments where segment_type='ROLLBACK';

从上面仅仅是查询到回滚段的基本信息，要了目前各个回滚段的动态信息，还要查询V$ROLLNAME和V$ROLLSTAT视图。V$ROLLNAME视图只存放各回滚段的编号和名字，V$ROLLSTATS存放各个回滚段当前的情况信息。要想得到每个回滚段的信息，就要查询两个表的信息才能得到。如：

select s.usn,n.name,s.extents,s.optsize,s.hwmsize,s.status from v$rollstat s, v$rollname n where s.usn=n.usn

2 查看回滚段的使用情况，哪个用户正在使用回滚段的资源(当提交或回滚后资源释放):

SELECT s.username, u.name FROM v$transaction t, v$rollstat r, v$rollname u, v$session s WHERE s.taddr = t.addr AND t.xidusn = r.usn AND r.usn = u.usn ORDER BY s.username;

3 回滚段当前活动的事物(事务提交或回滚后自动清空)

SELECT s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk  FROM v$session s,v$transaction t;

4 分析 UNDO 的使用情况

SELECT TABLESPACE_NAME,STATUS,TRUNC(SUM(BLOCKS) * 8 / 1024) AS "Size M",COUNT(*) Undo_Extent_Num FROM DBA_UNDO_EXTENTS GROUP BY TABLESPACE_NAME, STATUS;

5 监控undo表空间

SELECT BEGIN_TIME, END_TIME, UNDOTSN, UNDOBLKS, TXNCOUNT,MAXCONCURRENCY AS "MAXCON"　FROM V$UNDOSTAT;

6 查询是否有回滚段的争用

select * from v$waitstat;

SELECT name, waits, gets, waits/gets "Ratio" FROM v$rollstat a, v$rollname b WHERE a.usn = b.usn;

7 查看回滚段的统计信息：

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;

8 查询回滚段的事务回退率

transaction rollbacks/(transaction rollbacks+user commits)

select name,value from v$sysstat where name in('user commits','transaction rollbacks');

9 查询回滚段在使用,扩展,回缩的时候extent在循环的次数

select usn,wraps from v$rollstat;

10 查询回滚段收缩的情况

select usn,optsize,shrinks from v$rollstat;

创建回滚段

 

　　语法：

　　CREATE [PUBLIC] ROLLBACK SEGMENT rollback_segment

　　　　 [TABLESPACE tablespace]

　　　　 [STORAGE ([INITIAL integer[K|M]] [NEXT integer[K|M]]

　　　　　　　　　　 [MINEXTENTS integer]

　　　　　　　　　　 [MAXTENTS {integer|UNLIMITED}]

　　　　　　　　　　 [OPTIMAL {integer[K|M]|NULL}]) ]

　　注：

　　 回滚段可以在创建时指明PRIVATE或PUBLIC，一旦创建将不能修改。

　　 MINEXTENTS 必须大于等于2

　　 PCTINCREASE必须是0

　　 OPTIMAL如果要指定，必须大于等于回滚段的初始大小（由MINEXTENTS指定）

　　建议：

　　 一般情况下，INITIAL=NEXT

　　 设置OPTIMAL参数来节约空间的使用

　　 不要设置MAXEXTENTS为UNLIMITED

　　 回滚段应创建在一个特定的回滚段表空间内

 

参数的意义同建表上样，但这里不允许使用pctincrease参数，因此其增涨百分比总是零。这里的参数的使用（执行效果）与表的参数有很大的不同。一表中的数据在关机后仍放于该表空间所对应的数据文件中，而回滚段只在系统运行且在insert ， update ， delete时用，后即可释放（让别的transaction用），且在关机后所记录的所有回滚信息均被释放，因而就有扩展后的空间如何释放的问题，即保持多少空间为最佳的问题（由OPTIMAL）设定。Optimal可以避免“snapshot too old”错误的发生，为每个rollback segmal指定一个optimal以便在内存中保持一个较少的cache值，从而提高性能。

 

例：

create tablespace rbs datafile '/data/oradata/cts/rbs01.dbf' size 100M autoextend on next 10M maxsize 150M;

create public rollback segment rbs01 tablespace rbs storage(initial 100K next 100K minextents 10 maxextents 100 optimal 1000K);

select segment_name,tablespace_name,status from  dba_rollback_segs ;

 

但是：创建成功后也查询不到，因为从9i开始，回滚段开始自动管理，不需要手动创建

SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

 

如果想改回手动管理需要执行一下操作：

ALTER SYSTEM SET UNDO_MANAGEMENT=MANUAL  SCOPE=SPFILE

Alter system set undo_management=MANUAL scope=spfile;

shutdown immediate                            

startup                                       

select segment_name,owner,status from dba_rollback_segs;

 

 

改变回滚段

 

当回滚段建立好了之后，有时需要对它们进行修改。可以对回滚段进行存储参数的修改，可以对某个脱机（OFFLINE）回滚段修改为联机（ONLINE）。也可能对已经处于联机的回滚段设置为脱机，比如当我们进行IMP或大量修改数据时，ORACLE总是需要大的回滚段。但由于分配回滚段是ORACLE系统包办。为了在事务处理中得到大的回滚段，我们只能将较小的回滚段设置为脱机的状态，等到我们所处理的事务完成后在将它们设置为联机等。

 

 

设置实例重启动后回滚段联机

 

除了上面使用CREATE ROLLBACK SEGMENT命令完成回滚段的创建外，要使ORACLE系统关闭重新启动后仍然处于联机，还要在initsid.ora 参数文件中rollback_segments参数加上回滚段的名字，如：

  rollback_segments= (r0，r1，r2 )

 

使回滚段在线

 

　　当回滚段创建后，回滚段是离线的，不能被数据库使用，为了使回滚段被事务利用，必须将回滚段在线。可以用以下命令使回滚段在线：

　　ALTER ROLLBACK SEGMENT rollback_segment ONLINE;

　　例：

　　ALTER ROLLBACK SEGMENT rbs01 ONLINE;

　　为了使回滚段在数据库启动时自动在线，可以在数据库的参数文件中列出回滚段的名字。例如在参数文件中加入以下一行：

ROLLBACK_SEGMENT=(rbs01,rbs02)

select name,status,gets,waits from v$rollname,v$rollstat where v$rollstat.usn=v$rollname.usn;

 

回滚段的扩张（EXTEND）

 

当当前回滚段区的所有块用完而事务还需要更多的回滚空间时，回滚段的指针将移到下一个区。当最后一个区用完，指针将移到第一个区的前面。回滚段指针移到下一个区的前提是下一个区没有活动的事务，同时指针不能跨区。当下一个区正在使用时，事务将为回滚段分配一个新的区，这种分配称为回滚段的扩展。回滚段将一直扩展到该回滚段区的个数到达回滚段的参数MAXEXTENTS的值时为止。

 

回滚段扩展次数的限制

 

在比较旧的ORACLE版本里，回滚段的扩展次数受到一些限制，它与实例的块大小有关。比如：

当ORACLE 块＝2k时，maxextents     121

当ORACLE 块＝4k 时，maxextents     249

当ORACLE 块＝8k 时，maxextents     505

当ORACLE 块＝16k 时，maxextents   1017

当ORACLE 块＝32k 时，maxextents   2041

 

注：在ORACLE V7.3 以后，Maxextents 的扩展次数已被取消，你可以设得足够大。甚至可以设为 UNLIMITED ( =249 000 000 )。

 

 

回滚段的回收和OPTIMAL参数

 

OPTIMAL参数指明回滚段空闲时收缩到的位置，指明回滚段的OPTIMAL参数可以减少回滚段空间的浪费。

设置OPTIMAL参数

    由于回滚段是一个动态的对象，它在使用中根据增删改的数据量而增加（扩展），但在用完后就被释放，然后另外的事务可以用。为了避免某个回滚段在扩展后总占用量的空间，不利于另外的回滚段的扩展，ORACLE 提供了 optimal 参对回滚段进行控制，该参数的意义是回滚段在扩展过程中，当事务完成后（发commit，rollback后），将回滚段的大小按照optimal 值进性收缩。

    需要注意的是，optimal 参数的设置不能比已经分配的空间还小。也就是说，optimal 的设置要比初始的扩展的还大才正确。比如：

 

一般 optimal = minextents *  initial +

( minextents + n ) * next

 

其中，要求minextents>=1 ; n>= 1

 

例如：

CREATE ROLLBACK SEGMENT rbs21 tablespace rbs

STORAGE( initial 10m next 2m minextents 2 optimal 16M );

 

这里optimal 只能填 14m 或 16m 或18m 等，不能填12m,因为最小扩展为2次，即rbs21回滚段的开始分配已经是 10m + 2m = 12m ，根据optimal 要大于初始值的原则，所以optimal 至少从 14m开始。

 

例子：

systen回滚段的初始分配initial = 409600 ；

下一次分配 next = 57344；

最佳扩展值 optimal= null；

根据这样的情况，我们可以修改下一次分配的值为 1MB(=1024000字节)；修改最佳扩展值为：optimal = initial + 2 * next = 409600 + 2* 1024000 = 2457600  。所以修改SYSTEM回滚的命令为：

 

SQL> alter rollback segment  system  storage(next 1m optimal 2457600);

 

注：optimal 表示在事务处理当中，该回滚段由于增、删、改的数据量的需要而不断对回滚段进行扩展，以满足事务的要求，但是在事务处理完成后可以让回滚段缩小到一个最佳的范围内。这就是由回滚段的OPTIMAL参数来控制。一般optimal = initial + n* next , 且  n >1 。

 

 

修改回滚段的存储参数

 

　　可以使用ALTER ROLLBACK SEGMENT命令修改回滚段的存储参数（包括OPTIMAL，MAXEXTENTS）。

　　语法：

　　ALTER ROLLBACK SEGMENT rollback_segment

　　[STORAGE ([NEXT integer[K|M]]

　　　　　　 [MINEXTENTS integer]

　　　　　　 [MAXEXTENTS {integer|UNLIMITED}]

　　　　　　 [OPTIMAL {integer[K|M]|NULL}]) ]

　　例：

　　ALTER ROLLBACK SEGMENT rbs01 STORAGE (MAXEXTENTS 1000);

 

回收回滚段的空间

 

　　如果指定了回滚段的OPTIMAL参数，ORACLE将自动回收回滚段到OPTIMAL指定的位置。用户也可以手动回收回滚段的空间。

　　语法：

　　ALTER ROLLBACK SEGMENT rollback_segment SHRINK [TO integer [K|M]];

　　说明：

　　 如果不指明TO integer的数值，ORACLE将试图回收到OPTIMAL的位置。

　例：

　　ALTER ROLLBACK SEGMENT rbs01 SHRINK TO 2M;

 

使回滚段离线

 

select name,status,gets,waits from v$rollname,v$rollstat where v$rollstat.usn=v$rollname.usn;

 

　　为了达到以下两个目的将要回滚段离线：

　　1.阻止新的事务使用该回滚段；

　　2.该回滚段必须删除。

　　语法：

　　 ALTER ROLLBACK SEGMENT rollback_segment OFFLINE;

　　例：

　　 ALTER ROLLBACK SEGMENT rbs01 OFFLINE;

　　说明：

　　 如果有事务正在使用该回滚段，运行该命令后，回滚段的状态将是PENDING OFFLINE。事务结束后，状态将改为OFFLINE，可以通过V$ROLLSTAT查询回滚段的状态。

 

观察回滚段的增长

虽然回滚段在使用过程中作过扩展而在用完后又自动按照 OPTIMAL 要求大小缩小，但在动态字典V$ROLLSTAT 中会记录曾经扩展的情况。命令如下：

 

select   n.name, optsize, hwmsize from v$ROLLNAME n , V$ROLLSTAT s where n.usn=s.usn ;

 

optsize  是 最优大小值， hwmsize =Hight water mark of rollback segment  size 回滚段扩展中最高值（水位）。如果 optsize 为空，则 hwmsize 就是当前最高值。

 

 

增加 ORACLE 系统 rbs 表空间数据文件

 

alter tablespace rbs add datafile '/disk1/ORACLE/oradata/ora8/rbs02.dbf' size 120m ;

 

在事务中指定使用回滚段

 

由于回滚段建立好了后，在处理中回滚段的使用是由系统安排的，这样就有可能在处理中实际需要较大的回滚段而系统总是分配较小的回滚而导致处理失败。为避免这样的失败出现。就要在命令中或程序中用 SET TRANSACTION USE ROLLBACK SEGMENT命令来实现。

需要注意的是，在程序中使用设置命令时，要在程序的开始来使用 SET TRANSACTION 语句，而且在每次的COMMIT或ROLLBACK语句后都要重新使用SET TRANSACTION 语句，如果在COMMIT或ROLLBACK语句后不再使用SET TRANSACTION 语句，系统会释放原来被分配的回滚 段而随机分配新的回滚段。下面给出在SQL下和在PL/SQL下使用回滚段的例子。

 

在执行大事务时,有时oracle会报出如下的错误:

 

ORA-01555:snapshot too old (rollback segment too small)   

 

这说明oracle给此事务随机分配的回滚段太小了,这时可以为它指定一个足够大的回滚段,以确保这个事务的成功执行。

 

set transaction use rollback segment roll_abc;    

delete from table_name where ...    

commit;     

 

回滚段roll_abc被指定给这个delete事务,commit命令则在事务结束之后取消了回滚段的指定.

 

1.在SQL>下使用回滚段：

 

在SQLPLUS下进行任何大量的INSERT、UPDATE和DELETE 都要用到回滚段，如何你希望操作能成功的话，你应该在操作前指定要用大的回滚段。如：

 

Commit;

Set  transaction  use   rollback  segment    r1;

 

Delete from ...

Commit;

Set  transaction use rollback segment   r1;

 

例子：为大事务建立大的回滚段：

create rollback segment  interest   tablespace  interest_tabspace

storage( initial  50m  next 10m  optimal  80m  pct_increase 0 );

 

 

确定回滚段的数目

 

    回滚段的数量直接影响到系统的性能，如果回滚段的个数不够多，则在多个用户同时进行增、删、该时就存在等待现象。

 

要确定是否增加回滚段的数量，先要查询两个动态视图，即V$ROLLSTAT，V$WAITSTAT。如：

 

SQL> select * from v$waitstat where class='undo header';

 

CLASS                   COUNT       TIME

------------------ ---------- ----------

undo header                 0          0

 

SQL> select usn,extents,waits from v$rollstat;

 

       USN    EXTENTS      WAITS

---------- ---------- ----------

         0          5          0

         1          8          0

         2          8          0

         3          8          0

         4          8          0

         5          8          0

         6          8          0

         7          8          0

 

如果 waits 存在大于 0 的数，则需要增加回滚段的数量。一般回滚段的数量主要根据应用系统的类型来决定。比如一般的历史档案系统，由于其主要处理是查询。这类增、删、改相对较少的应用，可以建立较少的回滚段。而想银行、证券等应用。就需要很多的回滚段。那么到底要多少才相对就够了呢？ 下面的回答具有一般性：

 

在并发程度要求高的应用系统，同一时间内有多个 transaction 在竞争竟争回退段。假如transaction为事务的个数；则有：

 

n= transaction/transactions_per_rollback_segment

 

其中：

n= 回退段数量

transaction 为ORACLE 系统参数，系统中允许并发处理的最大transaction 数。

transactions_per_rollback_segment 为 ORACLE 参数，每个回退段允许同时写入的最大 transaction 数。

 

另外，如果从 v$waitstat 动态视图中查出自数据库启动后回退头的等待次数较高，则应该多建立回滚段。

 

创建通用回滚段

无论是任何类型的应用，建议重新建立新的回滚段。除了上面介绍的建立专门的回滚段以外，需要建立初始值，下次增加及最佳值合适的值。一般建议是 初始值 最好是 5MB以上，下次增长为 2MB至 5MB之间，最佳值在 20M至30MB之间。

 

删除已存在的回滚段 r01

 

　　当回滚段不再需要或要重建以改变INITIAL，NEXT或MINEXTENTS参数时，可以将其删除。要删除回滚段，不许使该回滚段离线。

语法：

　　DROP ROLLBACK SEGMENT rollback_segment;

例：

　　DROP ROLLBACK SEGMENT rbs01;

查询回滚段的信息

所用数据字典：DBA_ROLLBACK_SEGS

alter rollback segment r01 offline;

drop rollback segment r01;

 

 

回滚段使用量的估算

 

    如何保证有足够的回滚段，满足多个并发的transaction同时使用，又要考虑应有足够大的回滚段来满足特殊的transaction(如 long-runing transaction)的需要。这就是如何考虑回滚段的数量和大小的问题。除了system回滚段外，通常还要创建多个回滚段，通常较短的transaction 适于使用较小的回滚段，这将使系统将使系统有较好性能（因为大量的回滚信息可缓存于sga中。从而减少对硬盘的I/O）。而大的transaction则需要使用较大的回滚段，因为大量的回滚信息可以存入事先分配的extent中避免了动态分配空间；同时也防止transaction运行期间，用尽回滚段的空间后，而导致数据库出现ora-01562错误。

 

1、 回滚信息量

       存储于回滚段中的回滚信息量取决于transaction (insert， update， delete )类型和实际处理数据量，通常，insert向表中插入一第记录所产 生的回滚数据小于delele 从表中删除一条记录。因为回滚insert所产生的记录只是需删除它，而回滚一条删除记录需要重新插入该记录，前者在回滚段中只存储rowid，后者则存储了重新这条记录的所有信息。

 

2．回滚数据量的估计

   到目前为止，ORACLE不能提供一个很好的回滚段数据量的计算，作为dba可以 用下面方法进行估算：

1) 建立一个较小的测试表（数据来自实际表EMP）；

create table emp1  as select  *  from  emp where  deptno <=10;

 

2) 模仿（实际updeste 语句）transaction 作如下测试：

update emp1  set  deptno=1050  where  deptno<=100;

 

3)利用 v$rollstat

依据以上三步可以在 SQL> 下进行如下操作，从而得出基本回滚信息量：

SQL>select sum(writes) "begin"  from sys.v$rollstat;

SQL>update emp1 set deptno=1000 where deptno <=100;

 

SQL>select sum(writes)"end" from sys.v$rollstat;

 

小表的信息量= end - begin = test

 

实际 transaction 的回滚信息量 = test * (emp_row / emp1_row ) * 1.05

 

    由于一个 事务 的全部回滚信息可以写入多个 extent 中， 但每个回滚段的 extent 数量受到限制。所以在设置 storage 的 initial ， next 参数时要小心， 一般回滚段中的 extent 保持在 10  到  20 为理想。为了在 extent 后的空间部分被释放， 在 STORAGE  中提供 optimal 参数用以控制回滚段的大小。当回滚段需要分配更多的空间超过 optimal 值后， RDBMS 将检查回滚段的大小，一旦额外分配的extent 中无工作的 事务 且回滚段头正在指向 extent时，系统将释放这个 extent 回滚段的大小向回缩， 直至到 optimal 指定值。

 

回滚段的问题及解决方法

(1)事务要求的回滚段空间不够，表现为表空间用满（ORA-01560错误），回滚段扩展到达参数MAXEXTENTS的值（ORA-01628）。 

解决方法：
A. 扩大回滚段所在表空间
B. 设置较大的MAXEXTENTS参数
C. 为回滚段设置OPTIMAL参数
D. 用较大的EXTENT参数重新创建回滚段

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

ORA-01562: failed to extend rollback segment number 12

ORA-01628: max # extentsreached for rollback segment RBS12

扩大表空间

给回滚段表空间增加数据文件，并设置大回滚段apprbs的maxextents值为无限大：

alter tablespace rbs add datafile ‘/opt/oracle/db02/oradata/ORCL/rbs02.dbf‘ size 8192m autoextend on next 10m maxsize unlimited;

扩大参数
ALTER ROLLBACK SEGMENT rbs01 STORAGE (MAXEXTENTS 1000);
可用如下语句代替(批量提交释放回退段空间):

1

create table tt(id number,sal number,age number);

2

declare

begin

  for i in 1..10000 loop

    insert into tt values(i,i*10,i*100);

    end loop;

    end;

select * from tt order by id;

删除表tt中id不等于10的所有数据

3

begin

  loop

    delete from tt where id !=10 and rownum<=10;

    exit when sql%notfound;

    commit;

    end loop;

end;

其中rownum<=10的目的是每10条提交一次；

 

(2) ORA-01552 cannot use system rollback segment for non-system tablespace

 

'string'

原因: 没有可用的非系统回滚段. 分为以下情形:

A. 除了系统回滚段, 未创建其它回滚段

B. 只创建了PRIVATE回滚段, 但INITsid.ORA的ROLLBACK_SEGMENTS中未列出这些回滚段

C. 创建了PUBLIC回滚段, 但这些回滚段都处于OFFLINE状态

解决方法: 根据以上原因相应解决即可

 

(3) ORA_01555 snapshot too old: rollback segment number string with name "string" too small

原因可分为以下情形：

A. 回滚段太少/太小

数据库中有太多的事务修改数据并提交, 就会发生已提交事务曾使用的空间被重用, 从而造成一个延续时间长的查询所请求的数据已经不在回滚段中

(即：长查询开始之前，事务被修改并且没有提交，长查询进行中，事务提交，并且事务所在回滚段被其他事务覆盖，这时就会出现ora-01555错误)

解决方法: 创建更多的回滚段, 为回滚段设置较大的EXTENT以及较大的MINEXTENTS

 

B. 回滚段被破坏

由于回滚段被破坏, 造成事务无法将修改前的内容(read-consistent snapshot) 放入回滚段, 也会产生ORA-01555错误.

(即：事务被修改并且没有提交，之后事务所在回滚段损坏，这时在查询这个事务时就会报ora-01555错误)

解决方法: 将被破坏的回滚段OFFLINE, 删除重建.

 

C. FETCH ACROSS COMMIT

当一个进程打开一个CURSOR, 然后循环执行FETCH, UPDATE, COMMIT, 如果更新的表与FETCH的是同一个表, 就很可能发生ORA-01555错误.

 

解决方法:

 

a. 使用大的回滚段

 

b. 减少提交频率(可参见本论坛"如何避免一个PROCEDURE被重复调用"一贴中, 无名朋友的回帖)

以上两种方法只能减少该错误发生的可能, 不能完全避免. 如果要完全避免, 须从执行方法着手, 可以用以下两种方法:

 

c. 建立一个临时表, 存放要更新的表的查询列(如主键及相关的条件列), 从临时表FETCH, 更新原来的表.

 

d. 捕获ORA-01555错误, 关闭并重新打开CURSOR, 继续执行循环:

 

D. 延时块清除

* Delayed logging block cleanout(延时块清除)是ORACLE用来提高写性能的一种机制: 当修改操作(INSERT/UPDATE/DELETE)发生时, ORACLE将原有的内容写入回滚段, 更新每个数据块的头部使其指向相应的回滚段, 当该操作被COMMIT时, ORACLE并不再重新访问一遍所有的数据块来确认所有的修改, 而只是更新位于回滚段头部的事务槽来指明该事务已被COMMIT, 这使得写操作可以很快结束从而提高了性能接下来的任何访问该操作所修改的数据的操作会使先前的写操作真正生效, 从而访问到新的值. Delayed logging block cleanout 虽然提高了性能,但却可能导致ORA-01555. 这种情况下, 在OPEN/FETCH前对该表做全表扫描(保证所有的修改被确认)会有所帮助.

 

E 不适当的OPTIMAL参数:

太小的OPTIMAL参数会使回滚段很快被SHRINK, 造成后续读取操作访问时, 先前的内容已丢失，仔细设计OPTIMAL参数, 不要让回滚段过于频繁的EXTEND/SHRINK有助于问题的解决。

 

F  DB BLOCK BUFFER太小

如果读一致性所请求的块的先前内容在缓冲区中, 那么就不用去访问回滚段，而如果缓冲区太小, 使得先前版本的内容在CACHE中的可能性变小, 从而必须频繁的访问回滚段来获取先前的内容, 这将大大增大ORA-01555发生的可能。

 

 

 

 

 

oracle 块延迟清除(delayed block cleanout) 理解

为了保证事务的回退和满足多用户的CR， oracle引入了undo 机制， 由于undo是循环使用的，在一个事务完成过程中，它与redo相互配合，其中undo在一次事务中需要完成以下工作：

(1)     Transaction 开始前回滚段获取一个ITL(事务槽)，分配空间， 记录事务信息

(2)     Transaction 提交后，redo完成记录，同时还清除回滚段的事务信息 包括行级锁，ITL信息(commit 标志，SCN等)

清除这些事务段的信息的过程就叫做块清除， 在完成块清除时, 我们本事务修改的数据块就会存在两种可能

(1) 所有的数据块还保存在 buffer  cache 中

(2)部分数据块或者是全部数据块由于LRU管理已经被刷出了buffer cache。

oracle为了考虑到块清除的成本，以及性能，会作以下两种方式的块清除处理：

(1)快速块清除(fast block cleanout),  当事务修改的数据库全部保存在buffer cache 并且修改数据块的数据量没有超过 cache buffer  的 10%，快速清除事务信息。

(2)延迟块清除(delayed block cleanout) 当修改的数据块的阀值超过10%  或者本次事务相关的数据块已经被刷出了 buffer cache， oracle 会下次访问此block 时再清除事务信息。