Rollback Segment Count settings in Oracle Database

One caveat of altering _rollback_segment_count to a higher value and reducing is that Oracle will never drop/remove newly created rollback segments. The segments will be OFFLINE after you restart the database. I just did some experiments with _rollback_segment_count in Primary/Standby environment.

From Primary, we can see _rollback_segment_count is set to 0 and 11 rollback segments are there in the database.

SQL> SELECT a.ksppinm “Parameter”,
2 b.ksppstvl “Session Value”,
3 c.ksppstvl “Instance Value”
4 FROM x$ksppi a,
5 x$ksppcv b,
6 x$ksppsv c
7 WHERE a.indx = b.indx
8 AND a.indx = c.indx
9 AND a.ksppinm like ‘%_rollback_segment_count%’
10 /

Parameter Session Value Instance Value
—————————————- —————————— ——————————
_rollback_segment_count 0 0

Oracle will create minimum 10 (USER) ONLINE rollback segments by default.

SQL> select segment_name, status from dba_rollback_segs order by 1,2;

SEGMENT_NAME STATUS
—————————— —————-
SYSTEM ONLINE
_SYSSMU31_3054663620$ ONLINE
_SYSSMU32_2766567773$ ONLINE
_SYSSMU33_3504626774$ ONLINE
_SYSSMU34_141538141$ ONLINE
_SYSSMU35_2865072663$ ONLINE
_SYSSMU36_2829516047$ ONLINE
_SYSSMU37_1595015676$ ONLINE
_SYSSMU38_404694037$ ONLINE
_SYSSMU39_2413009061$ ONLINE
_SYSSMU40_2254133445$ ONLINE

11 rows selected.

On Standby also I see 11 Rollback segments.

SQL> SELECT a.ksppinm “Parameter”,
2 b.ksppstvl “Session Value”,
3 c.ksppstvl “Instance Value”
4 FROM x$ksppi a,
5 x$ksppcv b,
6 x$ksppsv c
7 WHERE a.indx = b.indx
8 AND a.indx = c.indx
9 AND a.ksppinm like ‘%_rollback_segment_count%’
10 /

Parameter Session Value Instance Value
—————————————- —————————— ——————————
_rollback_segment_count 0 0

SQL> select segment_name, status from dba_rollback_segs order by 1,2;

SEGMENT_NAME STATUS
—————————— —————-
SYSTEM ONLINE
_SYSSMU31_3054663620$ ONLINE
_SYSSMU32_2766567773$ ONLINE
_SYSSMU33_3504626774$ ONLINE
_SYSSMU34_141538141$ ONLINE
_SYSSMU35_2865072663$ ONLINE
_SYSSMU36_2829516047$ ONLINE
_SYSSMU37_1595015676$ ONLINE
_SYSSMU38_404694037$ ONLINE
_SYSSMU39_2413009061$ ONLINE
_SYSSMU40_2254133445$ ONLINE

11 rows selected.

Currently there are 11 rollback segments including the SYSTEM rollback segment. I am going to increase the rollback segment to 12 for this test.

On Primary

SQL> alter system set “_rollback_segment_count”=12;

System altered.

SQL> select segment_name, status from dba_rollback_segs order by 1,2;

SEGMENT_NAME STATUS
—————————— —————-
SYSTEM ONLINE
_SYSSMU1_4266230138$ ONLINE
_SYSSMU2_4124808705$ ONLINE
_SYSSMU31_3054663620$ ONLINE
_SYSSMU32_2766567773$ ONLINE
_SYSSMU33_3504626774$ ONLINE
_SYSSMU34_141538141$ ONLINE
_SYSSMU35_2865072663$ ONLINE
_SYSSMU36_2829516047$ ONLINE
_SYSSMU37_1595015676$ ONLINE
_SYSSMU38_404694037$ ONLINE
_SYSSMU39_2413009061$ ONLINE
_SYSSMU40_2254133445$ ONLINE

13 rows selected.

On standby

SQL> select segment_name, status from dba_rollback_segs order by 1,2;

SEGMENT_NAME STATUS
—————————— —————-
SYSTEM ONLINE
_SYSSMU1_4266230138$ ONLINE
_SYSSMU2_4124808705$ ONLINE
_SYSSMU31_3054663620$ ONLINE
_SYSSMU32_2766567773$ ONLINE
_SYSSMU33_3504626774$ ONLINE
_SYSSMU34_141538141$ ONLINE
_SYSSMU35_2865072663$ ONLINE
_SYSSMU36_2829516047$ ONLINE
_SYSSMU37_1595015676$ ONLINE
_SYSSMU38_404694037$ ONLINE
_SYSSMU39_2413009061$ ONLINE
_SYSSMU40_2254133445$ ONLINE

13 rows selected.

But the _rollback_segment_count is still 0 in Standby

SQL> SELECT a.ksppinm “Parameter”,
2 b.ksppstvl “Session Value”,
3 c.ksppstvl “Instance Value”
4 FROM x$ksppi a,
5 x$ksppcv b,
6 x$ksppsv c
7 WHERE a.indx = b.indx
8 AND a.indx = c.indx
9 AND a.ksppinm like ‘%_rollback_segment_count%’
10 /

Parameter Session Value Instance Value
—————————————- —————————— ——————————
_rollback_segment_count 0 0

I restarted the Standby database and it was still showing 13 online rollback segments.

Now let us see what happens if you reset _rollback_segment_count in Primary.

SQL> alter system reset “_rollback_segment_count”;

System altered.

SQL> select segment_name, status from dba_rollback_segs order by 1,2;

SEGMENT_NAME STATUS
—————————— —————-
SYSTEM ONLINE
_SYSSMU1_4266230138$ ONLINE
_SYSSMU2_4124808705$ ONLINE
_SYSSMU31_3054663620$ ONLINE
_SYSSMU32_2766567773$ ONLINE
_SYSSMU33_3504626774$ ONLINE
_SYSSMU34_141538141$ ONLINE
_SYSSMU35_2865072663$ ONLINE
_SYSSMU36_2829516047$ ONLINE
_SYSSMU37_1595015676$ ONLINE
_SYSSMU38_404694037$ ONLINE
_SYSSMU39_2413009061$ ONLINE
_SYSSMU40_2254133445$ ONLINE

13 rows selected.

Nothing happens. Newly created segments are still online after reset to Default value 0

Rollback segments CANNOT be decreased dynamically even the parameter “_rollback_segment_count” is set to less value or default. The Database bounce is required.

Let us bounce the Primary database. (Standby was still showing 13 Online segments)

On Primary:

SQL> select segment_name, status from dba_rollback_segs order by 1,2;

SEGMENT_NAME STATUS
—————————— —————-
SYSTEM ONLINE
_SYSSMU1_4266230138$ ONLINE
_SYSSMU2_4124808705$ ONLINE
_SYSSMU31_3054663620$ ONLINE
_SYSSMU32_2766567773$ ONLINE
_SYSSMU33_3504626774$ ONLINE
_SYSSMU34_141538141$ ONLINE
_SYSSMU35_2865072663$ ONLINE
_SYSSMU36_2829516047$ ONLINE
_SYSSMU37_1595015676$ ONLINE
_SYSSMU38_404694037$ ONLINE
_SYSSMU39_2413009061$ OFFLINE —->>> Both new segments become OFFLINE
_SYSSMU40_2254133445$ OFFLINE

13 rows selected.

On Standby:

SQL> select segment_name, status from dba_rollback_segs order by 1,2;

SEGMENT_NAME STATUS
—————————— —————-
SYSTEM ONLINE
_SYSSMU1_4266230138$ ONLINE
_SYSSMU2_4124808705$ ONLINE
_SYSSMU31_3054663620$ ONLINE
_SYSSMU32_2766567773$ ONLINE
_SYSSMU33_3504626774$ ONLINE
_SYSSMU34_141538141$ ONLINE
_SYSSMU35_2865072663$ ONLINE
_SYSSMU36_2829516047$ ONLINE
_SYSSMU37_1595015676$ ONLINE
_SYSSMU38_404694037$ ONLINE
_SYSSMU39_2413009061$ OFFLINE —->>> Both new segments become OFFLINE
_SYSSMU40_2254133445$ OFFLINE

13 rows selected.

Even after decreasing “_rollback_segment_count” OFFLINE segments will be available as OFFLINE for ever until you recreate the Undo Tablespace. Only way to get rid of these OFFLINE segment is create a new UNDO tablespace and switch undo table then drop the old undo tablespace. Remember, when you add new UNDO tablespace oracle will add _rollback_segment_count number of OFFLINE segments to new UNDO tablespace (if 0, then default (10) number of segments). If you have set a high number for _rollback_segment_count and your aim is to reduce the OFFLINE segments, you need to reset the _rollback_segment_count before adding new UNDO tablespace.