TEMP tablespace, resizing (ORA-01652) 
Author Message
 TEMP tablespace, resizing (ORA-01652)

Hi,

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 --



Wed, 18 Jun 1902 08:00:00 GMT
 TEMP tablespace, resizing (ORA-01652)

This might sound silly, but it happened to me, is your disk full, (or
almost)?


Quote:
> Hi,

> 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 --

Sent via Deja.com http://www.deja.com/
Before you buy.


Wed, 18 Jun 1902 08:00:00 GMT
 TEMP tablespace, resizing (ORA-01652)

Quote:

> This might sound silly, but it happened to me, is your disk full, (or
> almost)?

No there are 38Gb left!


Wed, 18 Jun 1902 08:00:00 GMT
 TEMP tablespace, resizing (ORA-01652)
Hi Sara,

  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

Quote:

> This might sound silly, but it happened to me, is your disk full, (or
> almost)?


> > Hi,

> > 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 --

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT
 TEMP tablespace, resizing (ORA-01652)

Quote:

> Hi Sara,

>   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

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



Wed, 18 Jun 1902 08:00:00 GMT
 TEMP tablespace, resizing (ORA-01652)

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



Wed, 18 Jun 1902 08:00:00 GMT
 TEMP tablespace, resizing (ORA-01652)
To update:

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



Wed, 18 Jun 1902 08:00:00 GMT
 TEMP tablespace, resizing (ORA-01652)
Sara, I had a similar problem running a script that created indexes on multiple
large tables (2 had 188 million records, 1 had 67 million records, 4 had
30-something million records, etc).  Even with a HUGE (40GB with autoextend on
maxsize unlimited) temp tablespace it still had the same error you encountered.
The only way I was able to get it to work was to run the index builds manually,
shutting down and restarting the database between index builds.
As I understand it, the reason this sorked is that the temporary extents
allocated in the temp tablespace while creating an index are not released when
the index is created.  They are only released when the database instance is shut
down.
HTH
Quote:

> To update:

> 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



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. ORA-01652: unable to extend temp segment by 256 in tablespace TEMP

2. ORA-01652: unable to extend temp segment by 256 in tablespace TEMP

3. ORA-01652: unable to extend temp segment by 512 in tablespace TEMP

4. ORA-01652 unable to extend temp segment by num in tablespace name

5. ORA-01652: unable to extend temp segment by 1598 in tablespace

6. ORA-01652: unable to extend temp segment by 1598 in tablespace

7. rebuild tablespace HELP ORA 01652

8. extend temp segment - ora 01652

9. 01652: unable to extend temp segment

10. Help ora-01652

11. Help ora-01652

12. ORA-01652 error


 
Powered by phpBB® Forum Software