Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Unable to create initial segment error

SOLVED
Go to solution
BGiulio
Occasional Contributor

Unable to create initial segment error

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
3 REPLIES
Kenneth_19
Trusted Contributor

Re: Unable to create initial segment error

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
Always take care of your dearest before it is too late
Brian Crabtree
Honored Contributor

Re: Unable to create initial segment error

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
Advisor
Solution

Re: Unable to create initial segment error

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
If in doubt, ask!