Home » RDBMS Server » Server Administration » Partioned index.(cannot extent segment size)
Partioned index.(cannot extent segment size) [message #294443] Thu, 17 January 2008 12:06 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Hi,i have a partitione index which is spaned across two tablespace.For one tablespace INDEX08 , i am getting error "cannot extent"
there is already 4GB space in INDEX08 Sad


follwing are the details on index

CREATE INDEX CARS.IE_NPGDATXREF_IDX_1 ON CARS.NPGDATXREF
(NPGADJ_NUM, CPGRP_NUM, BUNIT_NUM, PROD_NUM, NPGFORMULATYP_CD, 
NPGDATXREF_DT_START, NPGDATXREF_VALUE)
  LOGGING
LOCAL STORE IN (INDEX08,INDEX09,INDEX08,INDEX09,INDEX08,INDEX09,INDEX08,INDEX09)
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT );


select segment_name,next_extent from dba_segments
where tablespace_name='INDEX08' and segment_name='IE_NPGDATXREF_IDX_1';

IE_NPGDATXREF_IDX_1 201326592
IE_NPGDATXREF_IDX_1 201326592
IE_NPGDATXREF_IDX_1 201326592
IE_NPGDATXREF_IDX_1 201326592


Is thre anything more i can check ?had anyone come across anything such ?
Re: Partioned index.(cannot extent segment size) [message #294446 is a reply to message #294443] Thu, 17 January 2008 12:21 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
ofcourse there is a lot of space but not contiguous space.
Whats your extent size and upto what number does your segment unable to extend?
Try to alter the tablespace and decrease extent size or add more space to the tablespace.
Re: Partioned index.(cannot extent segment size) [message #294450 is a reply to message #294443] Thu, 17 January 2008 12:31 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Whats your extent size >>

select next_extent from dba_ind_partitions where partition_name='SYS_P147'

201326592


and the exact error i get is ..

ORA-1683: unable to extend index IE_NPGDATXREF_IDX_1 partition SYS_P147 by 24576 in tablespace INDEX08

but my questions,how this happens when there is 4 GB free space.
Re: Partioned index.(cannot extent segment size) [message #294455 is a reply to message #294443] Thu, 17 January 2008 12:38 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Because at 24577 there is data.
Quote:
ORA-01683: unable to extend index string.string partition string by string in tablespace string
Cause: Failed to allocate an extent of the required number of blocks for index segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

[Updated on: Thu, 17 January 2008 12:45] by Moderator

Report message to a moderator

Re: Partioned index.(cannot extent segment size) [message #294460 is a reply to message #294443] Thu, 17 January 2008 12:47 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Do you mean to say my 4GB is going to be waste always .any permanent fix to this ?
Re: Partioned index.(cannot extent segment size) [message #294462 is a reply to message #294443] Thu, 17 January 2008 12:53 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Whats your oracle version?
Re: Partioned index.(cannot extent segment size) [message #294463 is a reply to message #294455] Thu, 17 January 2008 12:54 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
varu123 wrote on Thu, 17 January 2008 13:38
Because at 24577 there is data.
Quote:
ORA-01683: unable to extend index string.string partition string by string in tablespace string
Cause: Failed to allocate an extent of the required number of blocks for index segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.



I don't think so.

24576 is the number of blocks, not the location. In 8k blocksize, this translates to 201326592 bytes.
Your first statement is correct, there is not 201326592 of contiguous space in the tablesspace.
Locally managed tablespsaces is the answer, or if the datafiles are not in autoextend mode, follow what the error message says.
Re: Partioned index.(cannot extent segment size) [message #294466 is a reply to message #294443] Thu, 17 January 2008 12:59 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
>>Oracle version 9.2.0.6.0


EXTENT MANAGEMENT is DICTIONARY for tablespace INDEX08. Do you think thts the proble here ?
Re: Partioned index.(cannot extent segment size) [message #294472 is a reply to message #294443] Thu, 17 January 2008 13:11 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Thank you Joy division,
This is the beauty of forums.If anyone commits a mistake,there is someone to correct it.

Now for the OPs question
Quote:
Do you mean to say my 4GB is going to be waste always

What do you have to say?
Re: Partioned index.(cannot extent segment size) [message #294533 is a reply to message #294443] Thu, 17 January 2008 23:22 Go to previous message
varu123
Messages: 754
Registered: October 2007
Senior Member
drop and recreate the index.
Previous Topic: Hw to change utl_file_dir parameter
Next Topic: problem in opening the databae
Goto Forum:
  


Current Time: Thu Dec 08 16:34:00 CST 2016

Total time taken to generate the page: 0.10066 seconds