Re: Create index on large table

This is a nice one!
Some remarks/suggestions
1 You didn't ask it not to use rollback segments, you asked it not to execute redo logging (roll forward)
2 any ddl issues an implicit commit, AFAIK before and after the DDL. If you issue a commit you will 'loose' the rollback segment you just set, which is exactly what we are seeing here.
3 I would look into the following issues - do you have the init<sid>.ora parameter sort_direct_writes set to auto or to true. This will bypass most Oracle layers - do you have your temporary tablespace set to a truly temporary one - do you have an appropiate initial and next extent in your default storage clause for that tablespace.
You issue might be one, where many new extents needs to be allocated. This is a transaction on the data dictionary. Of course transactions on the datadictionary are protected by the system rollback segment. 4 If this doesn't help, try issuing alter session set sql_trace true and post the results.
Then we will have the exacts statements, including the recursive sql (sql on the dictionary) and we will be probably able to resolve it.

Hth,
Sybrand Bakker, Oracle DBA

<pzankl_at_my-deja.com> wrote in message news:7mc75f$hdq$1_at_nnrp1.deja.com...
> Hi
>
> For I'm a newbie in the Oracle field I wanted to test some critical
> issues before burning my finger on hot data.
>
> I try to create an index on large table (18M entries, Oracle 8.0.5
> on Linux). After reading a lot of doc and fiddling around with
> (temporary) tablespaces and rollback segments I got stuck:
>
> SQL> select count(*) from links;
> COUNT(*)
> ---------
> 18036260
>
> SQL> commit;
> Commit complete.
>
> SQL> set transaction use rollback segment testrollbacksegment;
> Transaction set.
>
> SQL> create index LinksIndex on Links (IDH,IDL) tablespace test3space;
> create index LinksIndex on Links (IDH,IDL) tablespace test3space
> *
> ERROR at line 1:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-01562: failed to extend rollback segment number 3
> ORA-01650: unable to extend rollback segment R02 by 64 in tablespace RBS
>
> SQL> commit;
> Commit complete.
> SQL> create index LinksIndex on Links (IDH,IDL) tablespace test3space
> unrecoverable;
> create index LinksIndex on Links (IDH,IDL) tablespace test3space
> unrecoverable
> *
> ERROR at line 1:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-01562: failed to extend rollback segment number 3
> ORA-01650: unable to extend rollback segment R02 by 64 in tablespace RBS
>
> Why does Oracle complain about unextendible rollback segments when
> I try to tell it not to use them at all ? - Hmmpff ?
>
> Suggestions are really appreciated.
>
> THX, Peter
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Mon Jul 12 1999 - 04:34:16 CDT