Unable to create initial segment error

[BGiulio]

Hello dudes,

I'm facing a strange error with Oracle 8i when trying to perform an alter command.
Just to give you some details, datafiles tablespace have a lot of free space, table is little (1024k) but when I try

ALTER TABLE ---- ADD( UNIQUE (FIELDNAME));

it is retourned this error:

unable to create initial extents for segment in tablespace 'tablespacename'

Oracle manual reports as a solution to use another tablespace bigger, but this is not a matter of free space.

have you some insights on it?

Thanks already

[Kenneth_19]

Are you specifying a very large size for the initial extent of the table? Since this action needs a piece of contiguous space in the tablespace for allocating the initial extent, you might need to either:

1) reduce the size of the initial extent of the table, or
2) add a new datafile to the tablespace that can provide the contiguous space as definded for the table, or
3) perform a tablespace reorg to free-up or defragment the space.

Regards,
Kenneth

[Brian Crabtree]

Run this from sqlplus:

select max(bytes) from dba_free_space where tablespace_name = '';

This will tell you the maximum size for the extent that you are looking for.

Brian

[Kawah Cheung · Accepted solution]

Hi,

Try Specifying a "USING INDEX STORAGE" clause as part of the ALTER TABLE.

1. If the index has to be in the same tablespace as the table, define the
storage values as follows :

alter table tab_name add(unique(col_name) USING INDEX STORAGE (initial nK next nK));

2. If the index needs to be in a different tablespace than the table, specify the storage values as follows :

alter table tab_name add(unique(col_name) USING INDEX TABLESPACE tabsp_name STORAGE (initial nK next nK));

Rgds,
Kawah