I get an ORA-01652 (unable to extend temp segment by 5 in tablespace
TEMP) when I'm trying to create a large index in my database.

Querying v$sort_segment after it failed, the extent_size is 5,
total_extents is 24 and total_blocks is 124.

Querying dba_tablespaces and dba_data_files, I see that I have a single
datafile (size 500K) in that tablespace and an initial/next extents size
of 40K with max_extents of 505 and PCTINCREASE set to 0.  i.e. The
maximum number of full extents that can fit into that single datafile
are 12

I resized the datafile to 1M, however I got the exact same error (unable
to extend by 5)

My question is:  is there any way to find out how big to resize the
datafile to, in order to stop getting an error.  Obviously, I don't want
to size a datafile to 2 Gb permanently if it's only going to get used
for a really big sort once.  Or is there some other way of preventing
this error (i.e. setting SORT_AREA_SIZE at a different value?)

Thanks in advance for your help

Sarah

-- Running Oracle 8.0.5 on SGI IRIX 6.5 --

  You may have fragmented your free block list, try dropping and rebuilding
the
tablespace (temp doesn't hold anything permanent, you can do this one
before you
next backup without impace). If you only need this for a short time, create
a large temp2 tablespace,
alter the user which builds the index to use this as their temporary
tablespace, build
the index and then drop it.

  Note that such 'temporary' set-ups for DBA use are quite common. You may
wish to
build yourself different parameter files (init<SID>.ora) for maintenance
(larger sort_area_size ... ) than what you would normally do in production
mode.

 Also, modify your TEMP tablespace default storage parameters (alter
tablespace ...)
to be a multiple of your sort area size (default is 64k so set your initial
to be say 128k)
pct_increase should be 0 and your next should equal your initial.

  Hope this helps

;) guy

Thanks for the advice.

I just have a couple of things that I don't understand though:

If I have fragmented my free block list, would that show up in some view or
other?

Also, couldn't the simple reason for the error be that the index just needed
more space (in the datafile) for the sort?  I can't change MAX_EXTENTS to make
it larger than 505, giving me (with the current settings) a maximum size for
the tablespace of ~20M. Could I just try resizing the datafile to 20M, then?

Thanks again!

Sarah

The following query indicates my TEMP tablespace does not have holes:

SQL> select block_id, blocks, block_id+blocks from dba_extents where tablespace_name = 'TEMP' order by block_id;

  BLOCK_ID     BLOCKS BLOCK_ID+BLOCKS
---------- ---------- ---------------
      4162         20            4182
      4182         20            4202
      4202         20            4222
      4222         20            4242

If I were you, I would temporarily alter session set a larger sort_area_size and set the TEMP tablespace autoextend on and do the work. Meanwhile watch the datafile for the TEMP tablespace to see if it growing excessively.

BTW, saying "fragmented my free block list" sounds wierd. You mean fragmented your TEMP tablespace.

Yong Huang


    Hi Guy,
  Thanks for the advice.

  I just have a couple of things that I don't understand though:

  If I have fragmented my free block list, would that show up in some view or other?

  Also, couldn't the simple reason for the error be that the index just needed more space (in the datafile) for the sort?  I can't change MAX_EXTENTS to make it larger than 505, giving me (with the current settings) a maximum size for the tablespace of ~20M. Could I just try resizing the datafile to 20M, then?

  Thanks again!

  Sarah

I checked the fragmentation of the temp tablespace as suggested by Yong and
everything looked OK.

I did an ALTER database datafile 'temp01.dbf' resize 20M and then tried to
create the index again and it worked.

The main things that still confuse me are:

The script that I've been running adds a load of data to a table with two
columns  identical in size (OLD and NEW) using sqlloader.  It creates an
index on the OLD column and then does the same on the NEW column.  The first
CREATE_INDEX works but not the second one (when the datafile is 500K).
Because they are the "same size", one would logically expect that if the
size of the TEMP datafile is doubled, both indexes could be created?!

When I increased the size of the datafile to 20M, and the sort therefore
eventually worked, it tookup 161 extents.  Why would two seemingly identical
indexes take up such widely differing amounts of space during their
creation?

Thanks for all your help, by the way.  I altered my init.ora and default
storage as you suggested anyway.

/Sarah