Re: Unable to Create INITIAL extent for segment in TABLESPACE

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 Thu Oct 22 1998 - 18:47:53 CDT