| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Create index on large table ?
On Mon, 12 Jul 1999 07:50:48 GMT, pzankl_at_my-deja.com wrote:
>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
>
>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.
"SET TRANSACTION USE ROLLBACK SEGMENT xxx;" sets the use of the specified rollback segment only for the duration of the current transaction, that is up to the next COMMIT. When you execute a DDL statement, such as CREATE INDEX, there are always two implicit commits performed - one at the beginning of the execution and one at the end. This means that your current transaction, for which you set the RB, is terminated by the initial implicit commit of the DDL, and the DDL itself is executed in another transaction, and therefore grabs whichever RB segment it likes.
So your only option is to take all other RB segments offline, leaving only TESTROLLBACKSEGMENT online - this way your CREATE INDEX will have to use it ;-).
>THX, Peter
Jurij Modic <jmodic_at_src.si>
Certified Oracle DBA (7.3 & 8.0 OCP)
 |
 |