Re: Unable to Create INITIAL extent for segment in TABLESPACE

You may receive ORA-1658 in the following cases:

• doing a dml.
• creating an object.
• running Forms, Reports, or any CDE tool.
• running an application.

You may also encounter this error in another scenario.

If you receive this error in 7.1, there will be no message text. Instead you may get the following:

ORA-01658 : NOT found ; PRODUCT RDBMS FACILITY=ORA ; ARGUMENT,[temp]

In 7.2, you will see the text:

01658, 00000, "unable to create INITIAL extent for segment in tablespace %s"

// *Cause:  Failed to allocate an extent for cluster segment in tablespace.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
//         files to the tablespace indicated.

Problem Explanation

Oracle is trying to allocate an extent and cannot find a contiguous chunk of free space. This is the same as receiving an ORA-1547 or ORA-165X error.

ORA-01547, 00000, "failed to allocate extent of size %s in tablespace '%s'"

// *Cause: Tablespace indicated is out of space
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
//         files to the tablespace indicated or create the object in other
//         tablespace if this happens during a CREATE statement

This error does not necessarily indicate whether or not you have enough space in the tablespace, it merely indicates that Oracle could not find a large enough chunk of free space to fit the next extent in.

There are two ways to approach this problem:

• add a new datafile to the tablespace, or
• alter the storage parameter of the table

I suggest you also check the alert log because maybe you received ORA-1652 unable to extend temp segment by ........

Greg Parsons wrote:

> Hi Margaret,
> Your right 'initial extent set once and used once', yet what is happening is that the initial extent that is set when creating the tablespace is for the first extent of each table AS A DEFAULT. So, unless the initial extent of the table you are creating is explicitly defined
> during table creation, you could (and are) receive errors where the initial is too large for the amount of free space available in SMPTBS.
> When you try next, perform a 'alter tablespace SMPTBS coalesce' statement. Don't worry, no bad consequences to it. This will perform the coalescing of free space that SMON (because of some other reasons, pct_increase) may have not put together. Then, query the view
> sys.dba_free_space looking for the largest free space in blocks within that tablespace. 'select max(blocks) from sys.dba_free_space;' There are better scripts, however, they do essentially the same thing. They tell you the largest sized free block can fit the amount of data you
> want allocated for your new table. (Remember to multiply this number of blocks by the db_block_size. It can be found by querying 'select name, value from v$parameter where name = 'db_block_size')
>
> HTH,
> Greg
>
> Margaret Peacock wrote:
>
> > I just did a large amount of updates on my database. I am now getting the error
> >
> > Unable to create INITIAL extent for segment in tablespace SMPTBS
> >
> > What does this mean? I tried to do an alter database to increase the size of the extents for the tablespace, but that doesn't work (and isn't the initial extent set once and used once?) Now certain quieries are erroring. Please send advice as soon as possible. Thanks in advance.
> >
> > -Margaret
> > -----------------------------------------------------------------------------
> > Margaret Peacock ESN: 655-8559
> > Intranet Web Master EXTERNAL: (408) 565-8559
> > Nortel - Mission Park peacockm_at_nt.com
> > ----------------------------------------------------------------------------
Received on Fri Oct 23 1998 - 15:34:12 CDT