By default,
Maximum no. of extents are 121. Increase that size to all RBS by some max.
no, supported by your system.
"Lothar Armbrüster" <lothar.armbruester_at_rheingau.netsurf.de> wrote in
message news:3329.9T659T12302478_at_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 Tue Dec 07 1999 - 11:57:49 CST
