Unable to create initial extent for segment in tablespace

September 24, 2009

The other day I was using an Oracle database tablespace supplied by a dba for a development environment and when I went to rebuild the full text index the Repository Manager greeted me with this nifty little error:

ORA-01658: unable to create initial extent for segment in tablespace

Fun huh?

Ok, this is a development box, lots of space. I thought this was all setup to expand on need, but let's check it out. First, we need to go to the database via something like Toad, SQL Developer or JDeveloper and execute a Query similar to this one:

select file_name, bytes, autoextensible, maxbytes from dba_data_files where tablespace_name='DEVWCM_SYSTEM';

You're tablespace name will vary, BUT REMEMBER: the value you supply for tablespace name is case sensitive

Running this query I find out that autoextend is NOT turned on, and further exploration yields a maxed out datafile. Fine, let's get autoextend turned ON. Here's two samples:

alter database datafile 'C:\Oracle\app\oradata\orcl\DEVWCM_SYSTEM.DBF' autoextend on; alter database datafile 'C:\Oracle\app\oradata\orcl\DEVWCM_SYSTEM.DBF' autoextend on next 100m maxsize 2000m;

In the first case we're going to simply turn on autoextend and let it ride. In case number two you can see some extra instructions including how much to extend and a limit.

And did this take care of the problem? Yep. Sweet, a fresh, clean, working index.

© 2020 Jason Stortz (version 20200607-210013)