Re: Create rollback segment under ORACLE 9ir2 failed????

Mike, et.al,

    Mea Culpa. Please ignore my previous post. I failed to properly context switch from my Automatic Undo mode.

    IIRC, as of 7.3, the requirement for a second rollback segment in SYSTEM was removed, with minor exceptions. I think the one that is biting you is that a second RBS must be created in SYSTEM if the rollback segment tablespace is locally managed. This is the default for 9i, where the default for 8i was dictionary. Check the allocation_type for the tablespace. If it is not dictionary, drop and recreate the tablespace as dictionary or create the second rbs in system.

-- 
Daniel W. Fink
http://www.optimaldba.com

IOUG-A Live! April 27 - May 1, 2003 Orlando, FL
   Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL
   Wednesday, May 1 1:00pm - 2:00pm - Automatic Undo Internals


Pete Sharman wrote:



>Mike

>

>The only way this would have worked under 8i is if you had already

>created a dummy rollback segment in the SYSTEM tablespace.   Something

>like this should work (before or after the CREATE TABLESPACE

>rollback_space)

>

>SQL> connect / as sysdba;

>SQL> CREATE ROLLBACK SEGMENT dummy;

>

>Pete

>"Controlling developers is like herding cats."

>Kevin Loney, Oracle DBA Handbook

>"Oh no, it's not.  It's much harder than that!"

>Bruce Pihlamae, long-term Oracle DBA

> 

>

>

>-----Original Message-----

>Sent: Thursday, March 13, 2003 2:30 PM

>To: Multiple recipients of list ORACLE-L

>

>

>I am create database on ORACLE 9iR2 and fail on create

>rollback segment.

>

>SQL> create tablespace rollback_space datafile

>  2           '/u4/oradata/TRAN/rbs01TRAN.dbf'      

>size   800M          

>  3  default storage (

>  4          initial          256k

>  5          next             256k

>  6          pctincrease        0

>  7          minextents         8

>  8          MAXEXTENTS       4096

>  9   );

>

>Tablespace created.

>

>SQL>

>SQL> REM * Create rollback segments.

>SQL> REM *

>SQL> create rollback segment rollback_1 tablespace

>rollback_space

>  2       storage (initial 256K next 256k minextents

>20 optimal 5M);

>create rollback segment rollback_1 tablespace

>rollback_space

>*

>ERROR at line 1:

>ORA-01552: cannot use system rollback segment for

>non-system tablespace

>'ROLLBACK_SPACE'

>

>

>SQL> create rollback segment rollback_2 tablespace

>rollback_space

>  2       storage (initial 256K next 256k minextents

>20 optimal 5M);

>create rollback segment rollback_2 tablespace

>rollback_space

>*

>ERROR at line 1:

>ORA-01552: cannot use system rollback segment for

>non-system tablespace

>'ROLLBACK_SPACE'

>

>

>Those script used to work under ORACLE 8i.

>

>Does anyone know why?

>

>Thanks.

>

>

>__________________________________________________

>Do you Yahoo!?

>Yahoo! Web Hosting - establish your business online

>http://webhosting.yahoo.com

>  

>




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel W. Fink
  INET: optimaldba_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).