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

Ewald B?ger <news_at_ewald.hetnetniet.nl> wrote in message news:<MPG.168beaa9dbf3ff7f98968b_at_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!

$ oerr ora 01654
01654, 00000, "unable to extend index %s.%s by %s in tablespace %s" // *Cause: Failed to allocate an extent for index segment in tablespace.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.

It would appear that your tablespace that holds the index may well be full. Either rebuild the index in another tablespace that has sufficient room or increase the size of the current tablespace by adding a file. Depending on how data is inserted and deleted from the table in question rebuilding the index could potentially reclaim significant reusable space so you may want to do that before extending the tablespace. If so, take advantage of the operation to adjust the initial, next, and pctincrease parameters as advisable based on the space management policy in use and expected object size.

• Mark D Powell --