| Partioned index.(cannot extent segment size) [message #294443] |
Thu, 17 January 2008 12:06  |
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 
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 #294450 is a reply to message #294443] |
Thu, 17 January 2008 12:31  |
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 |
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 #294463 is a reply to message #294455] |
Thu, 17 January 2008 12:54 |
joy_division
Messages: 4963
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.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
