Re: unable to extent temp segment

Those 27 Oracle blocks need to be contiguous. From your select it appears you don't have 27 contiguous blocks.
As creating an index involves sorting, so usually results in creating a temporary segment, from what is happening we simply can not know, whether this problem occurred during the sort (and you are sorting in the same tablespace, as the index will be creared in, you don't seem to have nor a separate index tablespace nor a separate temp tablespace) or during the creation of the index, which also starts of as a temporary segment (before it is renamed to objects_2_i in this case). If you only have that little space free I would
- extend the usr tablespace

• move all the indexes to a separate tablespace
• assign proper initial and next extent values during creation of the index, in order to avoid fragmentation of both the index and the free space in the tablespace.
• make sure you dedicate a tablespace for temporary segments.

Hth,
Sybrand Bakker, Oracle DBA

    Grt wrote in message <374954C4.133FDFBA_at_hotmail.com>...     When creating an index in the USR tablespace I received the 'unable to extent message':

    SQL> create index objects_2_i
      2 on objects_2(obj#);
    on objects_2(obj#)

       *
    ERROR at line 2:
    ORA-01652: unable to extend temp segment by 27 in tablespace USR

    But DBA_FREE_SPACE said there was enough space (27 * 2048 =55296):

    SQL> select bytes

      2  from dba_free_space
      3  where TABLESPACE_NAME = 'USR';

         BYTES
    ----------
        151552
         53248
         20480

    Anyone? Received on Mon May 24 1999 - 09:13:40 CDT