Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2004-10-25
Medium Priority
Last Modified: 2008-01-09

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?

Question by:joehodge
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12400629
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).

Author Comment

ID: 12400728

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?

LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 500 total points
ID: 12400898
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.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question