Can't get rid of ORA-01654: unable to extend index

From: Ewald Börger <news_at_ewald.hetnetniet.nl>
Date: Thu, 20 Dec 2001 12:32:12 +0100
Message-ID: <MPG.168beaa9dbf3ff7f98968b@news.cs.utwente.nl>

Hi,

I keep getting a ORA-01654: unable to extend index .... by ... error. I've been looking around a bit, but I can't find any handy things to. Added problem: the company I work for only needs to work with Oracle in very selected occasions and thus has no in-house Oracle expertise
(except for some admin work like creating databases/tables/etc even
giving permissions to users and tablespaces is difficult for us.
(When a customer has Oracle, their expertise is used to help install our
application on their servers).

I myself am just a student, doing an assignment with this company (it's called an "industry placement" here).

problem is: I'm making a Java app which must be able to read a database from SQL Server and Oracle and export it to an XML file and vice versa
(recreate the database with data from XML in SQL Server or Oracle).
SQL Server part already works, I need to work on exporting from Oracle and recreating a SQL Server exported database in Oracle works, except for the error in the subject.
(and of course it needs to be completed before Christmas holiday!

My app works with JDBC to connect to Oracle.

What have I tried?
Well, quite unfamiliar with Oracle, I've of course read some documentation and used google to look for answers, but unfortunately I'm lacking time to read everything and all... I've tried to use STORAGE (MAXEXTENTS UNLIMITED) added to the create index statements, e.g.:
CREATE INDEX IX_ItemValues ON ewald_leeg.ItemValues (ItemID, Field) became
CREATE INDEX IX_ItemValues ON ewald_leeg.ItemValues (ItemID, Field) STORAGE (MAXEXTENTS UNLIMITED)
but that doesn't work (at least not that I could see).

I know the best answer to my question is "go and read a good book on Oracle" or "take some Oracle courses". Although I admit that would be best, I just don't have the time nor the resources to do that, and besides this problem everything else already works as it should.

relevant data:

// the table which is giving problems
CREATE TABLE ewald_leeg.ItemValues (

	ItemValueID number (10, 0) 
		constraint ItemValues_ItemValueID_NN not null, 
	ItemID number (10, 0) constraint ItemValues_ItemID_NN not null, 
	Field varchar2 (50), 
	FieldType varchar2 (50), 
	Value varchar2 (255), 
	constraint ItemValues_PK primary key (ItemValueID)

)

// the non-default indices defined on this table CREATE INDEX IX_ItemValues ON ewald_leeg.ItemValues (ItemID, Field) STORAGE (MAXEXTENTS UNLIMITED)
CREATE INDEX IX_ItemValues_1 ON ewald_leeg.ItemValues (ItemID, Value) STORAGE (MAXEXTENTS UNLIMITED)
CREATE INDEX IX_ItemValues_2 ON ewald_leeg.ItemValues (ItemID, Field, Value) STORAGE (MAXEXTENTS UNLIMITED)
CREATE INDEX IX_ItemValues_3 ON ewald_leeg.ItemValues (Field, Value, ItemID) STORAGE (MAXEXTENTS UNLIMITED)

// the views which use this table
CREATE VIEW ewald_leeg.ItemValues_2 AS

        SELECT * FROM ItemValues

CREATE VIEW ewald_leeg.Items_MSP AS

	SELECT Active_Items.ItemID, Active_Items.FolderID,
		Active_Items.ItemType 
	FROM Active_Items, Folders, ItemValues, ItemValues_2 
	WHERE (Active_Items.UpdateNr = 1) 
		AND (ItemValues.Field = 'MspProjectID') 
		AND (ItemValues_2.Field = 'MspTaskID') 
		AND (Active_Items.FolderID = Folders.FolderID) 
		AND (Active_Items.ItemID = ItemValues.ItemID) 
		AND (Active_Items.ItemID = ItemValues_2.ItemID)

CREATE VIEW ewald_leeg.ParentsAndChildren AS
	SELECT SubFolders.OrderNr, FolderTypes.FolderTypeName, 
		FolderTypes.ViewName, FolderTypes.FuncID, 
		FolderTypes.Action, FolderTypes.Adminonly, 
		FolderTypes.ItemType, FolderTypes.PageDef,
		Active_Folders.Enddate, Active_Folders.Startdate, 
		Active_Folders.FolderType, Active_Folders.Description, 
		Active_Folders.Name, Active_Folders.PersonID, 
		Active_Folders.Archived, Active_Folders.Datum, 
		SubFolders.ParentID, SubFolders.ChildID, 
		Active_Folders.FolderID, FolderTypes.NotAppMenu, 
		FolderTypes.NavBack 
	FROM Active_Folders, SubFolders, FolderTypes 
	WHERE (FolderTypes.ViewID = 0) 
		AND (Active_Folders.FolderID = SubFolders.ChildID) 
		AND (Active_Folders.FolderType = FolderTypes.FolderTypeID)

after this all the records are inserted, after about 3370 records (give or take a few) the following error pops up for the first time:

Error: in
INSERT INTO ewald_leeg.ItemValues

	(ItemValueID, ItemID, Field, FieldType) 
	VALUES ('133190', '14298', 'ProcessStep', 'String') 

java.sql.SQLException:
ORA-01654: unable to extend index EWALD_LEEG.IX_ITEMVALUES_3 by 13 in tablespace EWALD_LEEG

Then some inserts are handled without errors and then the same pops up and it keeps popping up more frequently until finally the rest (some 70000 records) are not inserted at all, but just generate the error.

The problem of course is that data isn't replicated, but also my program is slowed down considerably:
Table ItemValues took 1012 seconds for 75090 items which is a 75 inserts/sec rate
Table Items took 49 seconds for 5866 items which is a 120 inserts/sec rate
so if the errors whouldn't have popped up my program would have taken 10 minutes to complete it's job in stead of 19 minutes... (10 minutes is still too long, but speed is not a major issue)

Any help appreciated!

-- 
Ewald Börger

Received on Thu Dec 20 2001 - 05:32:12 CST