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

lhpapa

看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 可以单独挪出来，这样减少争用呢？

xifenfei

估计不行哦
系统启动的时候，会在特定的块中去找system中的undo

lhpapa

好像可以，我听别人讲过。说为了优化，把system表空间的undo挪了出来。
麻烦Liu说说啦？

hctech

system表空间中的undo在外部undo表空间正常的情况下是不会被使用的吧？它应该是在oracle启动创建数据字典的阶段时被使用的，正常情况下不会被其它操作占用，不存在什么性能问题吧？

lhpapa

补充：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 编辑 ]

Maclean Liu(刘相兵

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. "