chk dba_free_space show enough freespace in INDEX ts but index pk complain cant exten

[ngwh]

hi guys,

I got a funny situation here... I query my DBA_FREE_SPACE it shows enough freespace tablespace size of 600MB in my index table but when the user application try to perform some transaction it complain ORA-1654 and in the alert log it stated certain index pk cannot be extended....?? this is puzzling and i added another 300MB and the problem is solved??..wonder why?

hv u guy encounter this prob? my platform is Solaris 2.6 with Oracle 8.1.7.4 ...

[slimdave]

If you are using dictionary managed tablespaces, and haven't been careful to keep all extents of all segments exactly the same size in the TS's, then you might have fragmented free space.

check the system view dba_free_space_coalesced to find out -- also start planning your switch to locally managed TS's with uniform extent sizes.

[rajabalachandra]

hi,

if the tablespace is fragmented and you don't have
enough contiguous free space to fit the extent size of the
index, then you will get the error.

you can try decreasing next extents of the index to smaller size.

you can try coalescing the tablespace.
(only if the fragmentation is of honeycomb type -
that is the adjacent pieces free and large,
but logically separated as different pieces,
this will solve the problem).

[ngwh]

hi slimdave,

wondering if this mean i hv fragmented segment?

this is my result:

A)
select tablespace_name,round(sum(total_bytes/1024/1024),1) MB from dba_free_space_coalesced group by tablespace_name;

TABLESPACE_NAME MB
------------------------------ ----------
PROD_INDX 1123.9


B)
SQL> select tablespace_name,round(sum(BYTES_COALESCED/1024/1024),1) MB from dba_free_space_coalesced group by tablespace_name;

TABLESPACE_NAME MB
------------------------------ ----------
PROD_INDX 1110.3