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

"Alvaro Fuentes" <alvarof2_at_hotmail.com> a écrit dans le message de news:cd53hi$j6u$1_at_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,
>
> A. Fuentes
> 512-297-9937
>
>

Did you set undo_management to auto and undo_tablespace to your undo tablespace undo_1?

--
Regards
Michel Cadot