Home » RDBMS Server » Server Administration » Need help with ORA-1654 (8.1.7)
Need help with ORA-1654 [message #460770] Mon, 14 June 2010 11:46 Go to next message
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
Senior Member
Hi,

I am using 8.1.7. I received an error "ORA-1654: Unable to extend index "ULTGNP.PK_A_PT_ORDER_MASTER" by 25435 in tablespace "ULTGNP"

The script for the above index is:

CREATE UNIQUE INDEX ULTGNP.PK_A_PT_ORDER_MASTER ON ULTGNP.A_PT_ORDER_MASTER
(BUS_UNIT, REG_NO, ORDER_NO)
LOGGING
TABLESPACE ULTGNP
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          49816K
            NEXT             101740K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


There is about 7gb space left in the tablespace. Please let me know if you need more information to solve the problem.

I have created a new datafile, and it seems to solve the problem temporarily. Please guide me.

Best Regards,

Re: Need help with ORA-1654 [message #460771 is a reply to message #460770] Mon, 14 June 2010 11:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I have created a new datafile, and it seems to solve the problem temporarily. Please guide me.
Acceptable solution until more space is required again.
Re: Need help with ORA-1654 [message #460772 is a reply to message #460770] Mon, 14 June 2010 11:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
ORA-01654: unable to extend index %s.%s by %s in tablespace %s
 *Cause:  Failed to allocate an extent of the required number of blocks for
          an index segment in the tablespace indicated.
 *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
          files to the tablespace indicated.

Regards
Michel
Re: Need help with ORA-1654 [message #460773 is a reply to message #460770] Mon, 14 June 2010 12:07 Go to previous messageGo to next message
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
Senior Member
Hi,

I had enough space in other datafiles of ULTGNP tablespace. I can't understand why oracle didn't utilize free space in other datafiles?
Re: Need help with ORA-1654 [message #460774 is a reply to message #460773] Mon, 14 June 2010 12:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> I can't understand why oracle didn't utilize free space in other datafiles?
Perhaps if you used an Oracle version that is supported during this century, Oracle's behavior may be more acceptable to you.
Re: Need help with ORA-1654 [message #460775 is a reply to message #460773] Mon, 14 June 2010 12:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
ultgnp wrote on Mon, 14 June 2010 19:07
Hi,

I had enough space in other datafiles of ULTGNP tablespace. I can't understand why oracle didn't utilize free space in other datafiles?

Space must be contiguous.As BlackSwan said, old version, old behaviour.

Regards
Michel

Re: Need help with ORA-1654 [message #460908 is a reply to message #460770] Tue, 15 June 2010 08:08 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
ultgnp wrote on Mon, 14 June 2010 12:46


There is about 7gb space left in the tablespace. Please let me know if you need more information to solve the problem.

I have created a new datafile, and it seems to solve the problem temporarily. Please guide me.


Your index requires 100Mb of contiguous space for each extent after the first one. You didn't say how large the index is going to be. You do not have enough 100Mb chunks of free space in your tablespace.
Previous Topic: Check DB listener and Read Only from DB query
Next Topic: Oracle 9i Upgrade to 10g
Goto Forum:
  


Current Time: Fri Nov 29 10:37:04 CST 2024