Re: ORA-01654 Error Handling

From: <fitzjarrell_at_cox.net>
Date: 13 Sep 2006 06:55:05 -0700
Message-ID: <1158155705.380510.103100@i3g2000cwc.googlegroups.com>

beerora wrote:
> sybrandb wrote:
> > beerora wrote:
> > > Hi,
> > >
> > > My machine WINXP running oracle8i client. The server is oracle9i.
> > >
> > > In my appln, one table has 3652 rows. I delete all by SQL command:
> > > DELETE FROM myTable;
> > > After that, I've re-loaded the data by sqlLoader. Only 2868 rows can be
> > > inserted and for rest I got following error in log file.
> > >
> > > <<ORA-01654: unable to extend index HOME.PK_TB_ACCT by 64 in tablespace
> > > TS_HOME_USR01
> > > SQL*Loader-605: Non-data dependent ORACLE error occurred -- load
> > > discontinued.>>
> > >
> > > Can pls guide me to solve this?
> > >
> > > Thanks & Regards,
> > > beer
> >
> > Please stop being lazy, look up error 1654 in the error messages manual
> > and the documentation and work from there.
> >
> > DO NOT EXPECT THIS FORUM TO DO YOUR WORK AND/OR TO TEACH YOU ORACLE.
> >
> > --
> > Sybrand Bakker
> > Senior Oracle DBA

>

> Hello Sybrand,

>

> What I want to know is proper guidence on clearing doubts not annoying
> by in-descent replies.

>

> I have searched this error and got good deals. But I dont understand
> the cause.
> Before deletion, the rows in a table were more but it shrinked and
> couldn't allow to insert the same amount of rows which have been
> deleted before.

>

> MR. SYBRAND, PLS READ KEENFULLY MY POSTINGS BEFORE YOU RESPOND.
>
> Cheers...

This table you deleted from is not the ONLY table using this index tablespace; you have no space, or the space you DO have remaining isn't contiguous or it isn't of sufficient size to allow this index to extend. Or the free space you do have hasn't been coalesced. You could query dba_free_space for the 'offending' tablespace to see what is available, but the simplest solution is to add space to the tablespace.

No one can tell you what conditions you have which cause this error to occur; you haven't provided enough information to do that.

David Fitzjarrell Received on Wed Sep 13 2006 - 08:55:05 CDT