Unable to extend temp segment.

Hi,
When I do a analuze compute statistics Oracle gives me unable to extend temporary segment.

SQL> analyze table test compute statistics;
analyze table test compute statistics
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 27309 in tablespace TEMPORARY_DATA


But when I see the statistics from enterprise manager it shows that the size of the tablespace is 102 MB and used is 0.002 MB. If it hasnt reached its maximum limit why is it giving me unable to extend error.

I also queried to confirm the results of enterpise manager. Below is the output of the query.

select
2 b.file_id "file#",
3 b.tablespace_name "Tablespace name",
4 b.bytes "#Bytes",
5 (b.bytes-sum(nvl(a.bytes,0)))"#used",
6 sum(nvl(a.bytes,0))"#free",
7 (sum(nvl(a.bytes,0))/(b.bytes))*100"%Free"
8 from sys.dba_free_space a,sys.dba_data_files b
9 where a.file_id(+)=b.file_id
10 group by b.tablespace_name ,b.file_id,b.bytes
11 order by b.tablespace_name;

file# Tablespace name #Bytes #used #free %Free
--------- ------------------------------ --------- --------- --------- ---------
7 REC_DATA 104857600 1036288 103821312 99.011719
3 ROLLBACK_DATA 10485760 4917248 5568512 53.105469
1 SYSTEM 62914560 60721152 2193408 3.4863281
6 TEMP 52428800 2048 52426752 99.996094
4 TEMPORARY_DATA 112197632 2048 112195584 99.998175
5 USERS 377657344 377647104 10240 .00271145
2 USER_DATA 3145728 1753088 1392640 44.270833

Please suggest.

Thanks
Anurag

[Edited by anuragmin on 05-30-2001 at 11:20 AM]

Hi

do you have a temporary tablespace assigned to your user? check your user temp table space

sorry , whant i ment to say is check you user temp table space, it may not be the temp you think it is.

I checked it.

The default tablespace is users and the pemporary tablespace is temporary_data.

Thanks
Anurag

Check the size for the next extent in the storage parameters for the temporary tblspace. This error also occurs if the next extent is too small.

Chintz

Hi,
These are the storage parameters for the temporary Tablespace.

Initial Extent : 10KB
Next Extent: 10KB
pct Increase: 50 %
Maximum Extents 121

The table which I am trying to analyze here has more than 6 million records.

Thanks
Anurag

Whoa!! Initial and next are too small. Increase them to atleast 1M each. (Extent allocation is CPU intensive). Increase the size of the ts.

Hi Halo,

Is it ok to have pctincrease set to 50% for a temorary tablepsace.

Thanks
Anurag

pctincrease shold be 0 in TEMP tablespace so you ensure that initial = next, inital and next should be multiple of sort_area_size to improve I/O

anyway instead of check free space with OEM try this query

select sum(bytes) from dba_free_space where tablespace_name='TEMPORARY_DATA'

I wouldn't quite do that.

Hi,
This is what I get.

SQL> select sum(bytes) from dba_free_space where tablespace_name='TEMPORARY_DATA';

SUM(BYTES)
----------
104855552

I have increased the initial and next extent to 1MB and also made the pctincrease to 0.


Even then I am getting the error
SQL> analyze table test compute statistics;
analyze table test compute statistics
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 512 in tablespace TEMPORARY_DATA.

Thanks
Anurag

ops sorry I forgot to add something in the query

anyway query dba_free_space to find out the largest extent available in your tablespaces


select tablespace_name, max(bytes) "largest free extent" from dba_free_space
group by tablespace_name
/

may be you dont have enough free contiguos space for the tablespace temp to extend (it has enough free space but they are not contiguos)

This is what I get when i run the query.

SQL> select tablespace_name, max(bytes) "largest free extent" from dba_free_space
2 group by tablespace_name
3 /

TABLESPACE_NAME largest free extent
------------------------------ -------------------
REC_DATA 103821312
ROLLBACK_DATA 3008512
SYSTEM 1261568
TEMP 1054720
TEMPORARY_DATA 112195584
USERS 10240
USER_DATA 1392640


Also please note that Now I have assigned the TEMP tablespace to the user and its largest free extent is 1054720.

I am still getting the error.

BTW whats the best way to size the temporary tablespace rather than to keep on increasing its size every now and then. There should be some way where I can predict the temporary tablespace size requirements by doing some calculations.

Thanks
Anurag

Thanks
Anurag

[Edited by anuragmin on 05-30-2001 at 01:57 PM]

Anurag,

what version of oracle are you using. There is a bug in 8.1.5 that causes similar problems and has been fixed in 8.16 or 8.1.7

If you are using 8.1.5, try setting your temporary tablespace to permanent and try your query.

uday

[Edited by uday on 05-30-2001 at 01:59 PM]

Uday,
I am using Oracle 8.0.5 for windows NT.

Anurag

You know what, since you have pctincrease 50 previously and your inital and next were so small that your current NEXT might be huge! (actually I think this is what is happening)

I dont know a way to deallocate the sort segments in temporary tablespace except

restarting the database
or
recreate the temporary tablespace

if it´s a critical database I would create a new temporary tablespace with pctincrease 0 and assign that new temp tablespace to all users then drop the old temporary tablespace



hth

I increased the datafile size of the temporary table space to 300 MB and set the maximum extents values for the temporary table space to unlimited and it worked.

In the OEM it shows me that used is 261 MB.

It needs this much of space only while querying one particular table which has millions of rows in it. This is the only table in that schema.

Is it OK if I assign a specific temporary table space to it which no other user uses and define the storage parameters as Initial = 300MB so that it does not have to assign extents and will inturn improve performAnce.

Thanks
Anurag

Always set INITIAL and NEXT parameters value for the temp tablespace to multiples of SORT_AREA_SIZE.