You are out of space in the EWALD_LEEG tablespace. It is full. Add a data
file to the tablespace.
alter tablespace EWALD_LEEG add datafile
'FULLFILE_NAME_AND_PATH_OF_A_NEW_FILE' size xxM;
or allow the EWALD_LEEG to extend larger. Your DBA should be able to show
you.
Jim
"Ewald Börger" <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!
>
> --
> Ewald Börger
Received on Thu Dec 20 2001 - 07:56:31 CST
