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


I have a tablespace that is of size 500mb. There isn't much data in my db so im unsure why its giving me this error message when I try to create a table in this tablespace.

I'd like to know if I can find out why it has filled up so quickly and how I can increase its size.

Also, as a temporary workaround I've built two tables in the otif_index tablespace. can I switch the tablespaces easily?

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

slightwv (䄆 Netminder)Connect With a Mentor Commented:
The error is from attempting to create the 'initial' extent on object creation.  If oyu don't specify the initial extent at object create time it will default to the values of the tablespace.

I'm not a TOAD user so am unable to help from that perspective.  Does TOAD show you the SQL it is trying to execute?

Yes you can alter the storage parameters w/o losing data.  I will caution you that you need to be aware of the impact of these changes.  For optimal performance:  The storage parameters should be set up based on the data contained in the tablespace.  Messing with the initial and next extents at each object level could lead to massive fragmentation in the tablespace (assuming a lot of DML).

If you have a lot of objects in a tablespace and each one has different storage parameters and there is a lot of DML taking place:  You will encounter fragmentation (I've seen a tablespace that shows 5 Gig of freespace but not a single 2M extent exits....I didn't create the DB, was just in charge of making it work).

The worst thing that could happen with a coalesce, is a little more overhead for oracle to carve out new extents.
In a nutshell:
  All the objects in a tablespace have a 1M next extent size and the tablespace has 10 1M extents side by side.  The coalesce will make a single free extent of 10M.  Then the next time an object needs another 1M extent, it will take a minimal amount of time to carve it out of the 10M extent.

There are numerous scripts out there that will show you tablespace fragmentation (I don't have one handy or I'd post it.  A quick google search should locate many).  I've started using the tablespace mapping section of OEM (Oracle Enterprise Manager) for this so all my old DBA scripts have disappeared over the years.
slightwv (䄆 Netminder) Commented:
What is the initial_extent size of the object you are trying to create?

The initial extent must be contiguous disk space.  If you are pretty sure you have enough space, the tablespace may be fragmented.  Try: alter tablespace <tablespace_name> coalesce;

This will take side-by-side free extents and consolidate them into a single large extent.  If you still don't have enough room for the initial extent:  either resize the datafile or make the initial extent smaller.

alter tablespace <name> resize <somelargersize>;

ie./ alter tablespace TEMP resize 1000M;  -- in 10g you can use 1G

depending on your version you can move objects between tablespaces (check the docs for your version).
joehodgeAuthor Commented:

Im running Oracle9.2.

the initial extent is set to default kilobytes in Toad so I'm not sure how I find out this value. I looked at a selection of other tables and think I may have found out the issue?

            INITIAL          64K
            NEXT             15M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT

so the initial seems quite low but when it runs out of space it uses 15m!

could this be the cause? and if so can I alter this without losing any of the data within the tables?

alsi, will alter tablespace <tablespace_name> coalesce; cause anything nasty to happen? can I run anything to check to see if it is fragmented before I try this?

All Courses

From novice to tech pro — start learning today.