RE: help for rollback segments..

[Suhen Pather]

Saurabh,   First of all no rollback segment should be in the SYSTEM tablespace except SYSTEM rollback segment. This cause serious performance problems.   You would need to drop all the other rollback segments that are in the SYSTEM tablespace and recreate them in a ROLLBACK tablespace.   To drop rollback segment make sure no transactiosn are running. alter rollback segment SEGMENT_NAME offline;   drop rollback segment SEGMENT_NAME;   For each rollback segment and then recreate in the ROLLBACK tablespace. After creation the rollback segments will be offline. You would need to set them online individually.   The reason why the rollback segments are offline is probably because they are not included in your init.ora file.  Add all rollback segments to this file and restart instance.   You do not need to create the rollback segments as public rollback segments.  I think public Rollback segments are used mostly for OPS.   Regards Suhen HI ALL,   i've some confusion regarding rollback segments. could anyone explain this information from my database.   select * from dba_rollback_segs  revealed following stats..   SEGMENT_NAME  OWNER  TABLESPACE INITIAL_EX NEXT_EXTEN MIN_EXTENT MAX_EXTENT PCT_INCREA STATUS ------------- ------ ---------- ---------- ---------- ---------- ---------- ---------- ------- SYSTEM        SYS    SYSTEM          57344      57344          2        505          0 ONLINE SYSROL        SYS    SYSTEM         106496     106496          2        505          0 OFFLINE RB0           PUBLIC RBS            106496     262144          2        121          0 ONLINE RB1           PUBLIC RBS            106496     262144          2        121          0 ONLINE RB_TEMP       SYS    SYSTEM         106496     106496          2        121          0 OFFLINE RB2           PUBLIC SYSTEM          57344     262144          2        121          0 ONLINE RB3           PUBLIC SYSTEM          57344     262144          2        121          0 ONLINE RB4           PUBLIC SYSTEM          57344     262144          2        121          0 ONLINE RB5           PUBLIC SYSTEM          57344     262144          2        121          0 ONLINE RB6           PUBLIC SYSTEM          57344     262144          2        121          0 ONLINE RB7           PUBLIC SYSTEM          57344     262144          2        121          0 ONLINE RB8           PUBLIC SYSTEM          57344     262144          2        121          0 ONLINE RB9           PUBLIC SYSTEM          57344     262144          2        121          0 ONLINE RB10          PUBLIC SYSTEM          57344     262144          2        121          0 ONLINE RB11          PUBLIC SYSTEM          57344     262144          2        121          0 ONLINE RB12          PUBLIC SYSTEM          57344     262144          2        121          0 ONLINE RB13          PUBLIC SYSTEM          57344     262144          2        121          0 ONLINE RB14          PUBLIC SYSTEM          57344     262144          2        121          0 OFFLINE RB15          PUBLIC SYSTEM          57344     262144          2        121          0 OFFLINE RB16          PUBLIC SYSTEM          57344     262144          2        121          0   can anybody explain, why i get some status as offline. will this extent allocation and other parameters ok. i 've oracle 8.1.5 on NT, these stats are from my test database. why are they in system tablespace. i've two rollback tablespace of size 15 and 20 mb resp. should they be in system or in rollback. if yes, how to change their default tablespace.   pls help me in deciding that.   thanks in advance.   saurabh