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

Posted on 2004-10-25
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
    LVL 75

    Expert Comment

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


    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?

    PCTUSED    40
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                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 75

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

    877 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now