Oracle Manually manage rollback segments

1, view the parameters of the undo section

Sql> show parameter undo;

NAME TYPE VALUE

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

Undo_management string AUTO-> changed to Manual

Undo_retention Integer 900

Undo_tablespace string UNDOTBS1

Sql> Show parameter transactions; Affairs

NAME TYPE VALUE

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

Transactions integer 187 How many transactions are ready to support in the future system

Transactions_per_rollback_segment integer 5 How many transactions are supported for each rollback segment

Sql> Show Parameter rollback

NAME TYPE VALUE

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

Fast_start_parallel_rollback string Low

Rollback_segments String Private rollback segment

Transactions_per_rollback_segment Integer 5

Principle: Oracle's boot process, first will go to find some rollback_segments in the rollback, and will be calculated transactions/transactions_per_rollback_segment= 37 that is, 37 rollback segments are required to compare these two numbers and whether they are reorganized. If you reorganize the database open. If you do not regroup, you will find a common rollback segment. If not satisfied will also open the database, will not error, but in the future when the use of the error.

2. Manually establish the rollback section of undo

1) sql> shutdown immediate

The database has been closed.

The database has been unloaded.

The ORACLE routine has been closed.

2) manual modification of parameters

C:\oracle\admin\demo\pfile\init.ora

Undo_management=manual

3) Restart the database

Sql> Startup Pfile=c:\oracle\admin\demo\pfile\initdemo.ora

The ORACLE routine has started.

Total System Global area 167772160 bytes

Fixed Size 1247900 bytes

Variable Size 92276068 bytes

Database buffers 71303168 bytes

Redo buffers 2945024 bytes

Database loading complete.

The database is already open.

Restart a sqlplus window at the same time execute the following statement

Copyright (c) 1982, +, Oracle. All rights reserved.

Sql> Conn Sys/oracle@demo as SYSDBA;

is connected.

Sql> select * from Tests1;

Id

----------

12

55

Sql> Set Wrap off

Sql> Show Parameter Undo

NAME TYPE VALUE

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

Undo_management string MANUAL

Undo_retention Integer 900

Undo_tablespace string UNDOTBS1

sql> Delete Tests1

2 where id=12;

Delete tests1*

Line 1th Error:

ORA-01552: Non-system table space ' LYH ' cannot use System fallback segment

4) Create a rollback segment

Sql> Create rollback segment RBS1

2 tablespace UNDOTBS1;

The fallback segment was created.

Sql> Run

1 Create public rollback segment PRBS1

2* tablespace UNDOTBS1

The fallback segment was created.

sql> alter rollback segment RBS1 online;

The fallback segment has changed.

Sql> Show parameter rollback;

NAME TYPE VALUE

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

Fast_start_parallel_rollback string Low

Rollback_segments string

Transactions_per_rollback_segment Integer 5

5) Modify System parameters

C:\oracle\admin\demo\pfile\init.ora

Undo_management=manual

rollback_segments= (' rbs1 ', ' RBS2 ') if there are multiple such additions

transactions=100

transactions_per_rollback_segment=10

Sql> select * from Dba_rollback_segs;

Row is truncated

Segment_name OWNER Tablespace_name

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

System SYS System

PRBS1 Public UNDOTBS1

RBS1 SYS UNDOTBS1

_syssmu10$ Public UNDOTBS1

_syssmu9$ Public UNDOTBS1

_syssmu8$ Public UNDOTBS1

_syssmu7$ Public UNDOTBS1

_syssmu6$ Public UNDOTBS1

_syssmu5$ Public UNDOTBS1

_syssmu4$ Public UNDOTBS1

_syssmu3$ Public UNDOTBS1

Segment_name OWNER Tablespace_name

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

_syssmu2$ Public UNDOTBS1

_syssmu1$ Public UNDOTBS1

13 rows have been selected.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/