undo表空间管理测试1-----手动修改undo_management

最新推荐文章于 2022-11-23 22:40:22 发布

iteye_4185

最新推荐文章于 2022-11-23 22:40:22 发布

阅读量310

点赞数

文章标签：数据库

SYS@ORCL>show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900

undo_tablespace string UNDOTBS1

SYS@ORCL>select tablespace_name,segment_name,status from dba_rollback_segs;

TABLESPACE_NAME SEGMENT_NA STATUS
------------------------------ ---------- ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU10$ ONLINE
UNDOTBS1 _SYSSMU9$ ONLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU7$ ONLINE
UNDOTBS1 _SYSSMU6$ ONLINE
UNDOTBS1 _SYSSMU5$ ONLINE
UNDOTBS1 _SYSSMU4$ ONLINE
UNDOTBS1 _SYSSMU3$ ONLINE
UNDOTBS1 _SYSSMU2$ ONLINE
UNDOTBS1 _SYSSMU1$ ONLINE

11 rows selected.

SYS@ORCL>alter system set undo_management='MANUAL' scope=spfile;

System altered.

SYS@ORCL>show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SYS@ORCL>select name,value from v$spparameter where name='UNDO_MANAGEMENT';

no rows selected

SYS@ORCL>select name,value from v$spparameter where name='undo_management';

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
undo_management
MANUAL

SYS@ORCL>create rollback segment rbs1 tablespace undotbs1;

Rollback segment created.

SYS@ORCL>select tablespace_name,segment_name,status from dba_rollback_segs;

TABLESPACE_NAME SEGMENT_NA STATUS
------------------------------ ---------- ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU10$ ONLINE
UNDOTBS1 _SYSSMU9$ ONLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU7$ ONLINE
UNDOTBS1 _SYSSMU6$ ONLINE
UNDOTBS1 _SYSSMU5$ ONLINE
UNDOTBS1 _SYSSMU4$ ONLINE
UNDOTBS1 _SYSSMU3$ ONLINE
UNDOTBS1 _SYSSMU2$ ONLINE
UNDOTBS1 _SYSSMU1$ ONLINE

11 rows selected.

SYS@ORCL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORCL>startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 100664912 bytes
Database Buffers 180355072 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SYS@ORCL>select tablespace_name,segment_name,status from dba_rollback_segs;

TABLESPACE_NAME SEGMENT_NA STATUS
------------------------------ ---------- ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU10$ OFFLINE
UNDOTBS1 _SYSSMU9$ OFFLINE
UNDOTBS1 _SYSSMU8$ OFFLINE
UNDOTBS1 _SYSSMU7$ OFFLINE
UNDOTBS1 _SYSSMU6$ OFFLINE
UNDOTBS1 _SYSSMU5$ OFFLINE
UNDOTBS1 _SYSSMU4$ OFFLINE
UNDOTBS1 _SYSSMU3$ OFFLINE
UNDOTBS1 _SYSSMU2$ OFFLINE
UNDOTBS1 _SYSSMU1$ OFFLINE

11 rows selected.

SYS@ORCL>alter rollback segment rbs1 online;
alter rollback segment rbs1 online
*
ERROR at line 1:
ORA-01534: rollback segment 'RBS1' doesn't exist

不存在？？？明明上面已经创建了啊

执行过shutdown immediate , 再执行startup重启操作，回滚段就已经不存在了。 回滚段是临时保存的，一旦执行DDL或者DCL操作，或者执行commit指令，回滚段就会被抛弃。

明白了吧。。。

SYS@ORCL>show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string UNDOTBS1

SYS@ORCL>create rollback segment rbs1 tablespace undotbs1;

Rollback segment created.

SYS@ORCL>alter rollback segment rbs1 unline;
alter rollback segment rbs1 unline
*
ERROR at line 1:
ORA-02244: invalid ALTER ROLLBACK SEGMENT option

SYS@ORCL>alter rollback segment rbs1 online;

Rollback segment altered.

SYS@ORCL>select tablespace_name,segment_name,status from dba_rollback_segs;

TABLESPACE_NAME SEGMENT_NA STATUS
------------------------------ ---------- ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 RBS1 ONLINE
UNDOTBS1 _SYSSMU10$ OFFLINE
UNDOTBS1 _SYSSMU9$ OFFLINE
UNDOTBS1 _SYSSMU8$ OFFLINE
UNDOTBS1 _SYSSMU7$ OFFLINE
UNDOTBS1 _SYSSMU6$ OFFLINE
UNDOTBS1 _SYSSMU5$ OFFLINE
UNDOTBS1 _SYSSMU4$ OFFLINE
UNDOTBS1 _SYSSMU3$ OFFLINE
UNDOTBS1 _SYSSMU2$ OFFLINE

TABLESPACE_NAME SEGMENT_NA STATUS
------------------------------ ---------- ----------------
UNDOTBS1 _SYSSMU1$ OFFLINE

12 rows selected.

SYS@ORCL>show parameter rollback

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
rollback_segments string
transactions_per_rollback_segment integer 5
SYS@ORCL>update scott.emp set sal=sal+1;

14 rows updated.