lhpapa

86 积分	0 好友	2 主题

发消息

system表空间中是否有undo信息？如何移到单独的表空间？

1^#

发表于 2012-3-5 10:49:30 | 查看: 5910| 回复: 5

看DSI中有关于undo的部分描述：
The system RBU is primaryly reserved for actions against the SYSTEM tablespace,it is strongly recommeded to create additional undo segments in a dedicated tablespace.
是不是说system的RBU 可以单独挪出来，这样减少争用呢？

分享0

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

6^#

发表于 2012-3-5 21:50:37

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0    Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> col segment_name for a30

SQL> select segment_name,segment_type from dba_segments where segment_type='ROLLBACK';

SEGMENT_NAME                SEGMENT_TYPE
------------------------------ ------------------
SYSTEM                      ROLLBACK

SYSTEM 是唯一一个10g 以后的 rollback 类型的 segment 。

它在数据字典创建时被创建： SQL.BSQ，同时它也是关键的自举对象 bootstrap objects

reate tablespace SYSTEM datafile "D_DBFN"
  "D_DSTG" online
/
create rollback segment SYSTEM tablespace SYSTEM
  storage (initial 50K next 50K)
/

"@ The internals of the above explained in ‘kqlb.c’.

2. Now a query executed against the sys.bootstrap$ table, which holds the create sql’s for other base tables.

select line#, sql_text from bootstrap$ where obj# != :1 (56)

Subsequently it will create those objects by running those queries.

Object number 0 – (System Rollback Segment)
Object number 2 to 55 (Other base tables)
Object number 1 is NOT used by any of the objects.

3. Performs various operations to keep the bootstrap objects in consistent state."

注意system rollback segment是一种特殊的回滚段，在10g以后普通回滚段的类型都变成了”TYPE2 UNDO”，而唯有system rollback segment的类型仍为”ROLLBACK”，这是由其特殊性造就的:

SQL> col segment_name for a20
SQL> col rollback for a20
SQL> select segment_name,segment_type from dba_segments where segment_type='ROLLBACK';

SEGMENT_NAME       SEGMENT_TYPE
-------------------- ------------------
SYSTEM             ROLLBACK

http://www.oracledatabase12g.com ... e-about-commit.html

System rollback segment面向的是SYSTEM表空间上数据字典对象相关事务的数据，以及由对用户数据产生的递归SQL调用所产生的数据。
"

If the UNDO_TABLESPACE parameter is omitted, the first available undo tablespace in the database is chosen. If no undo tablespace is available, the instance will start without an undo tablespace. In such cases, user transactions will be executed using the SYSTEM rollback segment. You should avoid running in this mode under normal circumstances.

The system rollback segment will be used only for data for the system tablespace and data that is being created from recursive SQL calls generated by user data. "

回复只看该作者道具举报

lhpapa

5^#

发表于 2012-3-5 12:38:17

补充：startup的时候也会用于recovery。
TABLESPACE_NAME SEGMENT_NAME STATUS
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU1$ ONLINE
UNDOTBS1 _SYSSMU2$ ONLINE
UNDOTBS1 _SYSSMU3$ ONLINE
UNDOTBS1 _SYSSMU4$ ONLINE
UNDOTBS1 _SYSSMU5$ ONLINE
UNDOTBS1 _SYSSMU6$ ONLINE
UNDOTBS1 _SYSSMU7$ ONLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU9$ ONLINE
UNDOTBS1 _SYSSMU10$ ONLINE
UNDOTBS1 _SYSSMU11$ ONLINE
UNDOTBS1 _SYSSMU12$ ONLINE
UNDOTBS1 _SYSSMU13$ ONLINE

[ 本帖最后由 lhpapa 于 2012-3-5 14:06 编辑 ]

回复只看该作者道具举报