背景
为了进一步研究undo segment header block中的事务SLOT重用机制,需要多个不同会话使用相同的undo segment header block,本文应时而生。结论
1,oracle隐含参数相当牛比,我认为分析及加深对于它的理解有几个维度a,查询与某个关键字匹配的隐含参数,进行测试总结
b,通过MOS查找BUG找到某些隐含参数,一般情况下,这些隐含参数用于临时解决或屏蔽BUG的,也可以顺势理解隐含参数的含义
c,网上其它大牛的BLOG,获取分析一些隐含参数的函数
d,因为数据库迁移或升级,配置一些隐含参数,比如关闭好多新特性,这也是你了解它的一个重要窗口
e,还有其它的方式,欢迎交流与补充
2,在aum即撤消表空间自动管理模式下,可以通过配置参数 _smu_debug_mode 手工指定会话事务使用指定的回滚段
3,_smu_debug_mode的含义,请见mos:420525.1
这里引申一个重要方法:当你研究技术到一定程度时,通过某个关键字到MOS即可以查到更有价值的文章或方法,或者在GOOGLE中查
4,也可以手工管理撤消表空间模式下,手工为会话事务指定回滚段
不过这个回滚段必须是手工创建的回滚段
5,在手工管理撤消表空间模式下,除了SYSTEM ROLLBACK SEGMENT,其它的回滚段在数据库刚启动时,全是OFFLINE
6,在手工管理撤消表空间模式下,原来基于自动管理撤消表空间模式下UNDO TBS的回滚段不能手工ONLINE及OFFLINE
我理解这是处于2几种机制的考虑,更进一步的理解还要测试
7, _smu_debug_mode 必须以系统层面方可生效即ALTER SYSTEM ,大家且不要在生产上调整,这里我仅是为了测试
8,undo_management参数调整必须重启库
测试
SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
_smu_debug_mode 0 - set debug event for testing SMU ope
rations
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU1$ ONLINE
_SYSSMU2$ ONLINE
_SYSSMU3$ ONLINE
_SYSSMU4$ ONLINE
_SYSSMU5$ ONLINE
_SYSSMU6$ ONLINE
_SYSSMU7$ ONLINE
_SYSSMU8$ ONLINE
_SYSSMU9$ ONLINE
_SYSSMU10$ ONLINE
11 rows selected.
SQL> conn scott/system
Connected.
SQL> create table t_undo_seg(a int);
Table created.
SQL> insert into t_undo_seg values(1);
1 row created.
SQL> select xidusn,xidslot,xidsqn from v$transaction;
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
3 42 219
SQL> select usn,name from v$rollname where usn=3;
USN NAME
---------- ------------------------------
3 _SYSSMU3$
--新启一会话,手工指定和上述事务相同的回滚段
SQL> set transaction use rollback segment "_SYSSMU3$";
Transaction set.
SQL> insert into t_undo_seg values(3);
1 row created.
---可见没有实现指定会话使用指定的回滚段
SQL> select xidusn,xidslot,xidsqn from v$transaction;
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
3 42 219
2 30 208 ---
---undo处于AUM模式
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
--- _smu_debug_mode无法在会话级别修改,只能以系统层面修改
SQL> alter session set "_smu_debug_mode"=45;
alter session set "_smu_debug_mode"=45
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option
SQL> alter system set "_smu_debug_mode"=45;
System altered.
---再次在会话中指定回滚段
SQL> set transaction use rollback segment "_SYSSMU3$";
Transaction set.
SQL> insert into t_undo_seg values(3);
1 row created.
---这下发现已经成功手工指定回滚段
SQL> select xidusn,xidslot,xidsqn from v$transaction;
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
3 42 219
3 14 221
---再看看在撤消表空间手工模式下,如何手工指定回滚段
SQL> alter system set "_smu_debug_mode"=0;
System altered.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set undo_management='manual';
alter system set undo_management='manual'
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set undo_management='manual' scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 901775360 bytes
Fixed Size 2100424 bytes
Variable Size 226493240 bytes
Database Buffers 666894336 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
---可见手工管理撤消表空间,数据库启动后,除了SYSTEM ROLLBACK SEGMENT,其它的回滚段全是OFFLINE
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU1$ OFFLINE
_SYSSMU2$ OFFLINE
_SYSSMU3$ OFFLINE
_SYSSMU4$ OFFLINE
_SYSSMU5$ OFFLINE
_SYSSMU6$ OFFLINE
_SYSSMU7$ OFFLINE
_SYSSMU8$ OFFLINE
_SYSSMU9$ OFFLINE
_SYSSMU10$ OFFLINE
11 rows selected.
SQL> alter rollback segment "_SYSSMU5$" online;
alter rollback segment "_SYSSMU5$" online
*
ERROR at line 1:
ORA-30017: segment '_SYSSMU5$' is not supported in MANUAL Undo Management mode
SQL> host oerr ora 30017
30017, 00000, "segment '%s' is not supported in %s Undo Management mode"
// *Cause: the type of the specified undo segment is incompatible with
// the current undo management mode of the instance.
// *Action: Check the undo segment name and the undo management mode
// and reissue statement if necessary
SQL> select usn,name from v$rollname;
USN NAME
---------- ------------------------------
0 SYSTEM
SQL> desc v$rollstat;
Name Null? Type
----------------- -------- ------------
USN NUMBER
LATCH NUMBER
EXTENTS NUMBER
RSSIZE NUMBER
WRITES NUMBER
XACTS NUMBER
GETS NUMBER
WAITS NUMBER
OPTSIZE NUMBER
HWMSIZE NUMBER
SHRINKS NUMBER
WRAPS NUMBER
EXTENDS NUMBER
AVESHRINK NUMBER
AVEACTIVE NUMBER
STATUS VARCHAR2(15)
CUREXT NUMBER
CURBLK NUMBER
SQL> select count(*) from v$rollstat;
COUNT(*)
----------
1
SQL> create rollback segment manual_undo_seg1 tablespace undotbs1;
Rollback segment created.
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU1$ OFFLINE
_SYSSMU2$ OFFLINE
_SYSSMU3$ OFFLINE
_SYSSMU4$ OFFLINE
_SYSSMU5$ OFFLINE
_SYSSMU6$ OFFLINE
_SYSSMU7$ OFFLINE
_SYSSMU8$ OFFLINE
_SYSSMU9$ OFFLINE
_SYSSMU10$ OFFLINE
SEGMENT_NAME STATUS
------------------------------ ----------------
MANUAL_UNDO_SEG1 OFFLINE
12 rows selected.
---仅手工创建的回滚段方可手工ONLINE或OFFLINE
SQL> alter rollback segment "MANUAL_UNDO_SEG1" online;
Rollback segment altered.
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU1$ OFFLINE
_SYSSMU2$ OFFLINE
_SYSSMU3$ OFFLINE
_SYSSMU4$ OFFLINE
_SYSSMU5$ OFFLINE
_SYSSMU6$ OFFLINE
_SYSSMU7$ OFFLINE
_SYSSMU8$ OFFLINE
_SYSSMU9$ OFFLINE
_SYSSMU10$ OFFLINE
SEGMENT_NAME STATUS
------------------------------ ----------------
MANUAL_UNDO_SEG1 ONLINE
12 rows selected.
SQL> select usn,name from v$rollname;
USN NAME
---------- --------------------------------------------------
0 SYSTEM
11 MANUAL_UNDO_SEG1
---会话手工指定
SQL> set transaction use rollback segment "MANUAL_UNDO_SEG1";
Transaction set.
SQL> insert into t_undo_seg values(888);
SQL> select xidusn,xidslot,xidsqn from v$transaction;
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
11 1 2
1 row created.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-1852494/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-1852494/