ORACLE 8i增加回滚段

今天同事要求增加一个8i库的回滚段，这个8i库是所有系统中唯一一个8i，历史悠久。

该库OLAP和OLTP业务都有。

 

8i库接触太少了，只记得是手工管理。上网查了下：

 

语法：
1.
　　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}]) ]

2.
ALTER ROLLBACK SEGMENT rollback_segment ONLINE;

 

最后操作步骤：

1.查看当前回滚段

SQL>desc dba_rollback_segs;

SQL> col segment_name for a20
SQL> col tablespace_name for a20
SQL> set linesize 200

SQL> select * from dba_rollback_segs;

回滚段都在RBS RBS2两个TABLESPACE里，这2个表空间各22G.

 

2.回滚段都包含RBS关键字.适当增加MAXEXTENTS，设置OPTIMAL. 增加最大可扩展的空间，设定尽可回收到的一个值。

SQL> set pagesize 0
SQL> select 'ALTER ROLLBACK SEGMENT '||segment_name||' STORAGE(MAXEXTENTS 8192 OPTIMAL 100M);'  from dba_rollback_segs where segment_name like '%RBS%';

 

3.增加10个回滚段。

CREATE public  ROLLBACK SEGMENT rbs20 TABLESPACE rbs STORAGE ( INITIAL 1m NEXT 1M MINEXTENTS 2 MAXEXTENTS 8192 OPTIMAL 100M);
CREATE public  ROLLBACK SEGMENT rbs21 TABLESPACE rbs STORAGE ( INITIAL 1m NEXT 1M MINEXTENTS 2 MAXEXTENTS 8192 OPTIMAL 100M);
CREATE public  ROLLBACK SEGMENT rbs22 TABLESPACE rbs STORAGE ( INITIAL 1m NEXT 1M MINEXTENTS 2 MAXEXTENTS 8192 OPTIMAL 100M);
CREATE public  ROLLBACK SEGMENT rbs23 TABLESPACE rbs STORAGE ( INITIAL 1m NEXT 1M MINEXTENTS 2 MAXEXTENTS 8192 OPTIMAL 100M);
CREATE public  ROLLBACK SEGMENT rbs24 TABLESPACE rbs STORAGE ( INITIAL 1m NEXT 1M MINEXTENTS 2 MAXEXTENTS 8192 OPTIMAL 100M);
CREATE public  ROLLBACK SEGMENT rbs25 TABLESPACE rbs STORAGE ( INITIAL 1m NEXT 1M MINEXTENTS 2 MAXEXTENTS 8192 OPTIMAL 100M);
CREATE public  ROLLBACK SEGMENT rbs26 TABLESPACE rbs STORAGE ( INITIAL 2m NEXT 2M MINEXTENTS 2 MAXEXTENTS 8192 OPTIMAL 100M);
CREATE public  ROLLBACK SEGMENT rbs27 TABLESPACE rbs STORAGE ( INITIAL 2m NEXT 2M MINEXTENTS 2 MAXEXTENTS 8192 OPTIMAL 100M);
CREATE public  ROLLBACK SEGMENT rbs28 TABLESPACE rbs STORAGE ( INITIAL 2m NEXT 2M MINEXTENTS 2 MAXEXTENTS 8192 OPTIMAL 100M);
CREATE public  ROLLBACK SEGMENT rbs29 TABLESPACE rbs STORAGE ( INITIAL 2m NEXT 2M MINEXTENTS 2 MAXEXTENTS 8192 OPTIMAL 100M);

ALTER ROLLBACK SEGMENT rbs20 ONLINE;
ALTER ROLLBACK SEGMENT rbs21 ONLINE;
ALTER ROLLBACK SEGMENT rbs22 ONLINE;
ALTER ROLLBACK SEGMENT rbs23 ONLINE;
ALTER ROLLBACK SEGMENT rbs24 ONLINE;
ALTER ROLLBACK SEGMENT rbs25 ONLINE;
ALTER ROLLBACK SEGMENT rbs26 ONLINE;
ALTER ROLLBACK SEGMENT rbs27 ONLINE;
ALTER ROLLBACK SEGMENT rbs28 ONLINE;
ALTER ROLLBACK SEGMENT rbs29 ONLINE;

 

 

4.修改初始参数

ROLLBACK_SEGMENT=(...,...,新的回滚段)

使回滚段在库重启后自动ONLINE.

 

总结：

增加8i回滚段，不如9i后的方便。

几个回滚段的存储参数解释：

 

INITIAL :
Specify the size of the first extent of the object. Oracle allocates space for this extent when you create the schema object.

NEXT
Specify in bytes the size of the next extent to be allocated to the object.

MINEXTENTS
Specify the total number of extents to allocate when the object is created. This parameter lets you allocate a large amount of space when you create an object, even if the space available is not contiguous. The default and minimum value is 1, meaning that Oracle allocates only the initial extent, except for rollback segments, for which the default and minimum value is 2. The maximum value depends on your operating system.

MAXEXTENTS:
Specify the total number of extents, including the first, that Oracle can allocate for the object. The minimum value is 1 except for rollback segments, which always have a minimum of 2. The default value depends on your data block size.

OPTIMAL
This specifies the `optimal' size of a rollback segment in bytes.  When set,
Oracle will try to keep the segment at the specified size, rounded up to the
extent boundary.  The RDBMS tries to have the fewest number of extents such
that the total size is greater than or equal to the size specified as OPTIMAL.
If additional space is needed beyond the optimal size, the rollback segment
will expand beyond optimal to accommodate the current transaction(s), but will
eventually deallocate extents to shrink back to this size.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/703237/viewspace-673036/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/703237/viewspace-673036/