Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> 10g/AIX5.2: catch22: using a system rollback for a non-system tablespace with AUTO mngmt

10g/AIX5.2: catch22: using a system rollback for a non-system tablespace with AUTO mngmt

From: Alvaro Fuentes <alvarof2_at_hotmail.com>
Date: Thu, 15 Jul 2004 00:16:11 -0500
Message-ID: <cd53hi$j6u$1@ausnews.austin.ibm.com>

Fellow Oracle Users:

I tried to create a cluster schema in a non-system tablespace
"my_tablespace" which has AUTO segment space management:

SQL>
   create cluster my_cluster (

   1 c_id number,
   2 c_d_id number,
   3 c_w_id number,

   4
   5 )
   6 single table
   7 hashkeys XXXX
   8 hash is ( (c_id * ( 100 * 10 ) + c_w_id * 10 + c_d_id) )    9 size 350
  10 pctfree 0 initrans 3
  11 storage ( buffer_pool recycle ) parallel ( degree 4 )   12 tablespace my_tablespace;
create cluster my_cluster (
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'my_tablespace'

Now, thats true, the undo tablespace was created as sys when the database was created:

"...create undo tablespace undo_1 datafile

   '/roll1/roll1' size 512M reuse blocksize 8K;

set echo off
"

Also, the tablespace "my_tablespace" was created by user "my_user" (other than sys or system)

OK, I checked ORA-01552 in the manuals:

"ORA-01552, 00000, "cannot use system rollback segment for non-system
tablespace '%s'"
Cause: Tried to use the system rollback segment for operations involving

          non-system tablespace
Action: Create one or more private/public segment(s), shutdown and then

	   startup again. May need to modify the init.ora parameter
	   rollback_segments to acquire private rollback segment"


So I did,

create public rollback segment my_new_roll1 tablespace my_tablespace storage (initial 200k
  minextents 2 next 200k)
*
ERROR at line 1:
ORA-30574: Cannot create rollback segment in tablespace with AUTO segment space
management

Seems that I have run into a catch 22 situation.

Any ideas/advice how to create this cluster schema IN this non-system tablespace which has AUTO segment space management?

Any settings I should add/modify in my p_file?

Thanks in advance,

  1. Fuentes 512-297-9937
Received on Thu Jul 15 2004 - 00:16:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US