| UNDO表空间手工管理 | DBA之路：OoNiceDream |

UNDO表空间手工管理

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/03/undo-management-manual.html

在DBA 2.0都到来的时代，还谈UNDO表空间手工管理，实在是有点土了。
可惜这东西，真没接触过，做点简单的笔记，备用，没准还有机会用上。

1、9i中无法在ASSM下创建回滚段

SQL> select * from v$version where rownum < 2;
 
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
 
SQL> show parameter undo
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
undo_management                      string      MANUAL
undo_retention                       integer     3600
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1
SQL> CREATE TABLESPACE UNDO_TBS01
  2  datafile '/oradata/DBA/undo_ts01.dbf' size 100M autoextend off
  3  EXTENT MANAGEMENT LOCAL 
  4  AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
 
Tablespace created.
 
SQL> CREATE ROLLBACK SEGMENT rbs_one
  2  tablespace UNDO_TBS01;
CREATE ROLLBACK SEGMENT rbs_one
*
ERROR at line 1:
ORA-30574: Cannot create rollback segment in tablespace with AUTO segment space management

2、创建回滚段步骤：
需要system表空间创建一个回滚段，online后，才能在指定的回滚表空间上创建回滚段，否则会报：ORA-01552。

SQL> CREATE TABLESPACE UNDO_TBS02
  2  datafile '/oradata/DBA/undo_ts02.dbf' size 100M autoextend off
  3  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100K;
 
Tablespace created.
 
SQL> CREATE TABLESPACE UNDO_TBS03
  2  datafile '/oradata/DBA/undo_ts03.dbf' size 100M autoextend off
  3  EXTENT MANAGEMENT LOCAL 
  4  AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL;
 
Tablespace created.
 
SQL> CREATE ROLLBACK SEGMENT rbs_one
  2  tablespace UNDO_TBS03;
CREATE ROLLBACK SEGMENT rbs_one
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'UNDO_TBS03'
 
SQL> CREATE ROLLBACK SEGMENT rbs_sys
  2  tablespace system;
 
Rollback segment created.
 
SQL> alter ROLLBACK SEGMENT rbs_sys online;
 
Rollback segment altered.
 
SQL> CREATE ROLLBACK SEGMENT rbs_one
  2  tablespace UNDO_TBS03;
 
Rollback segment created.
 
SQL> CREATE ROLLBACK SEGMENT rbs_two
  2  tablespace UNDO_TBS02;
 
Rollback segment created.
 
SQL> drop rollback segment RBS_SYS;
drop rollback segment RBS_SYS
*
ERROR at line 1:
ORA-01545: rollback segment 'RBS_SYS' specified not available
 
SQL> alter rollback segment RBS_SYS offline;
 
Rollback segment altered.
 
SQL> drop rollback segment RBS_SYS;
 
Rollback segment dropped.
 
SQL> alter rollback segment RBS_ONE online;
 
Rollback segment altered.
 
SQL> alter rollback segment RBS_TWO online;
 
Rollback segment altered.
 
SQL> select SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,INITIAL_EXTENT,NEXT_EXTENT,MAX_EXTENTS,STATUS
  2  from dba_rollback_segs;
 
SEGMENT_NAME    OWNER  TABLESPACE_NAME SEGMENT_ID INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS STATUS
--------------- ------ --------------- ---------- -------------- ----------- ----------- ----------------
SYSTEM          SYS    SYSTEM                   0          57344       57344         505 ONLINE
_SYSSMU1$       PUBLIC UNDOTBS1                 1         131072                   32765 OFFLINE
_SYSSMU2$       PUBLIC UNDOTBS1                 2         131072                   32765 OFFLINE
_SYSSMU3$       PUBLIC UNDOTBS1                 3         131072                   32765 OFFLINE
_SYSSMU4$       PUBLIC UNDOTBS1                 4         131072                   32765 OFFLINE
_SYSSMU5$       PUBLIC UNDOTBS1                 5         131072                   32765 OFFLINE
_SYSSMU6$       PUBLIC UNDOTBS1                 6         131072                   32765 OFFLINE
_SYSSMU7$       PUBLIC UNDOTBS1                 7         131072                   32765 OFFLINE
_SYSSMU8$       PUBLIC UNDOTBS1                 8         131072                   32765 OFFLINE
_SYSSMU9$       PUBLIC UNDOTBS1                 9         131072                   32765 OFFLINE
RBS_ONE         SYS    UNDO_TBS03              11        2097152                   32765 ONLINE
RBS_TWO         SYS    UNDO_TBS02              12         212992      106496       32765 ONLINE
 
12 rows selected.
 
SQL>

3、PUBLIC回滚段与非PUBLIC的区别
两者的区别在于，如果未指定rollback_segments参数，重启后，非PUBLIC要手工oneline，而PUBLIC的回滚段则不需要。

SQL> create public rollback segment RBS_THREE
  2  tablespace UNDO_TBS02
  3  storage(optimal 10M);
 
Rollback segment created.
 
SQL> select SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,INITIAL_EXTENT,NEXT_EXTENT,MAX_EXTENTS,STATUS
  2  from dba_rollback_segs;
 
SEGMENT_NAME    OWNER  TABLESPACE_NAME SEGMENT_ID INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS STATUS
--------------- ------ --------------- ---------- -------------- ----------- ----------- ----------------
SYSTEM          SYS    SYSTEM                   0          57344       57344         505 ONLINE
_SYSSMU1$       PUBLIC UNDOTBS1                 1         131072                   32765 OFFLINE
_SYSSMU2$       PUBLIC UNDOTBS1                 2         131072                   32765 OFFLINE
_SYSSMU3$       PUBLIC UNDOTBS1                 3         131072                   32765 OFFLINE
_SYSSMU4$       PUBLIC UNDOTBS1                 4         131072                   32765 OFFLINE
_SYSSMU5$       PUBLIC UNDOTBS1                 5         131072                   32765 OFFLINE
_SYSSMU6$       PUBLIC UNDOTBS1                 6         131072                   32765 OFFLINE
_SYSSMU7$       PUBLIC UNDOTBS1                 7         131072                   32765 OFFLINE
_SYSSMU8$       PUBLIC UNDOTBS1                 8         131072                   32765 OFFLINE
_SYSSMU9$       PUBLIC UNDOTBS1                 9         131072                   32765 OFFLINE
RBS_THREE       PUBLIC UNDO_TBS02              10         212992      106496       32765 OFFLINE
RBS_ONE         SYS    UNDO_TBS03              11        2097152                   32765 ONLINE
RBS_TWO         SYS    UNDO_TBS02              12         212992      106496       32765 ONLINE
 
13 rows selected.
 
SQL> 
SQL> alter rollback segment RBS_THREE online;
 
Rollback segment altered.
 
SQL> startup force
ORACLE instance started.
 
Total System Global Area  657418472 bytes
Fixed Size                   735464 bytes
Variable Size             335544320 bytes
Database Buffers          318767104 bytes
Redo Buffers                2371584 bytes
Database mounted.
Database opened.
SQL> select SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,INITIAL_EXTENT,NEXT_EXTENT,MAX_EXTENTS,STATUS
  2  from dba_rollback_segs
  3  where SEGMENT_NAME like 'RBS%';
 
SEGMENT_NAME    OWNER  TABLESPACE_NAME SEGMENT_ID INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS STATUS
--------------- ------ --------------- ---------- -------------- ----------- ----------- ----------------
RBS_ONE         SYS    UNDO_TBS03              11        2097152                   32765 OFFLINE
RBS_THREE       PUBLIC UNDO_TBS02              10         212992      106496       32765 ONLINE
RBS_TWO         SYS    UNDO_TBS02              12         212992      106496       32765 OFFLINE
 
SQL> 
SQL> show parameter rollback_segments 
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_rollback_segments                integer     97
rollback_segments                    string
 
SQL> alter system set rollback_segments='RBS_ONE','RBS_TWO' scope=spfile;
 
System altered.
 
SQL> startup force 
ORACLE instance started.
 
Total System Global Area  657418472 bytes
Fixed Size                   735464 bytes
Variable Size             335544320 bytes
Database Buffers          318767104 bytes
Redo Buffers                2371584 bytes
Database mounted.
Database opened.
SQL> select SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,INITIAL_EXTENT,NEXT_EXTENT,MAX_EXTENTS,STATUS
  2  from dba_rollback_segs
  3  where SEGMENT_NAME like 'RBS%';
 
SEGMENT_NAME    OWNER  TABLESPACE_NAME SEGMENT_ID INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS STATUS
--------------- ------ --------------- ---------- -------------- ----------- ----------- ----------------
RBS_ONE         SYS    UNDO_TBS03              11        2097152                   32765 ONLINE
RBS_THREE       PUBLIC UNDO_TBS02              10         212992      106496       32765 ONLINE
RBS_TWO         SYS    UNDO_TBS02              12         212992      106496       32765 ONLINE
 
SQL>

4、让事物使用指定回滚段
使用：set transaction use rollback segment segment_name;命令，可让事物使用指定的回滚段

SQL> set transaction use rollback segment RBS_TWO;
 
Transaction set.
 
SQL> insert into ss values(1);
 
1 row created.
 
SQL> select xidusn,xidslot,used_ublk,used_urec from v$transaction;
 
    XIDUSN    XIDSLOT  USED_UBLK  USED_UREC
---------- ---------- ---------- ----------
        12         11          1          1
 
SQL> select usn,xacts,status from v$rollstat where xacts<>0;
 
       USN      XACTS STATUS
---------- ---------- ---------------
        12          1 ONLINE
 
SQL> select * from v$rollname;
 
       USN NAME
---------- ------------------------------
         0 SYSTEM
        11 RBS_ONE
        12 RBS_TWO
        10 RBS_THREE