create index and rollback segs?

From: Lothar Armbrüster <lothar.armbruester_at_rheingau.netsurf.de>
Date: 06 Dec 99 20:30:13 +0100
Message-ID: <3329.9T659T12302478@rheingau.netsurf.de>

Hello out there,

we have migrated our Oracle 7.3 database to 8i (8.1.5). After some problems we decided to reimport the schema that holds most of our production data from an export file. The schema was dropped and newly create with create user. Then the import was done.

Looking in the log file, I came across some strange behaviour.
Some index creations failed because of full rollback segmrnts.
I reran these statements but they also failed the second time.

Here is some transcript:

-----------------------> cut <---------------------------

SQL>
SQL> commit;

Transaktion mit COMMIT abgeschlossen.

 real: 0
SQL> set transaction use rollback segment rb_big;

Transaktion wurde gesetzt.

 real: 20
SQL>
SQL> CREATE INDEX "DB96C"."IX_GP_VERSAND_DT_VERS_ID" ON "GP_VERSAND"   2 ("DT_VERSAND_ID" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL   3 255989760 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 FREELISTS   4 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS_IND" NOLOGGING ; CREATE INDEX "DB96C"."IX_GP_VERSAND_DT_VERS_ID" ON "GP_VERSAND"

                                                   *

FEHLER in Zeile 1:

ORA-00604: Fehler auf rekursiver SQL-Ebene 1 
ORA-01562: Fehler beim Erweitern der Rollback-Segmentnummer 10. 
ORA-01628: Max. Anzahl von Extents (121) für Rollback-Segment RB9 erreicht 

 real: 1297406
SQL>
SQL> commit;

Transaktion mit COMMIT abgeschlossen.

 real: 0
SQL> set transaction use rollback segment rb_big;

Transaktion wurde gesetzt.

 real: 0
SQL>
SQL> CREATE INDEX "DB96C"."IX_GP_VERSAND_GP_NR_BETR" ON "GP_VERSAND"   2 ("GP_NR_BETRIEB" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL   3 466616320 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0   4 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS_IND"   5 NOLOGGING ;
CREATE INDEX "DB96C"."IX_GP_VERSAND_GP_NR_BETR" ON "GP_VERSAND"

                                                   *

FEHLER in Zeile 1:

ORA-00604: Fehler auf rekursiver SQL-Ebene 1 
ORA-01562: Fehler beim Erweitern der Rollback-Segmentnummer 12. 
ORA-01628: Max. Anzahl von Extents (121) für Rollback-Segment RB11 erreicht 

 real: 1921453
SQL>
SQL> commit;

Transaktion mit COMMIT abgeschlossen.

 real: 0

SQL> --set transaction use rollback segment rb_big;
SQL> 
SQL> --CREATE INDEX "DB96C"."IX_GP_VERSAND_GP_NR" ON "GP_VERSAND" ("GP_NR" )
SQL> --PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 534773760 NEXT 10485760
SQL> --MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
SQL> --BUFFER_POOL DEFAULT) TABLESPACE "TS_IND" NOLOGGING ;
SQL> 
SQL> spool off

-------------------------> cut <---------------------------

The third index that is commented out was created without errors.

I have some problems with this behaviour because I always thought that a DDL command like create index does not need any rollback segment. Additionally I wonder why RB12 runs out of extents when I tell the transaction to use RB_BIG which is able to hold the whole index including the table data.

At last some facts about the table:

ca. 13,000,000 rows filling ca. 500MB in the tablespace.

The platform is NT4.0 SP3

Any ideas?

Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Hauptstr. 26            | lothar.armbruester_at_t-online.de
D-65346 Eltville        |

Received on Mon Dec 06 1999 - 13:30:13 CST