Oracle Database Discussions

We have a temporary tablespace group TEMP_GROUP made of the following pre-existing temp files. I have placed the size in MB in brackets. Names have been changed to protect our privacy. NLS is spanish

SQL> select * from dba_tablespace_groups;

TEMP_GROUP TEMP1 --(1024)
TEMP_GROUP TEMP2 --(2000)
TEMP_GROUP TEMP3 --(8048)

This tablespace group is the default temp tablespace of this database, and is the default temp tablespace of sys in the example that follows

connect sys/password

alter INDEX schema1.idx1 rebuild
*
ERROR en línea 1:
ORA-01652: no se ha podido ampliar el segmento temporal con 128 en el tablespace TEMP1
-- this coinicdes with the TEMP1 showing 100% used

NOTE that the message refers to the tempfile TEMP1 and not the TEMP_GROUP, which has 11GB of space available

The size of the index is small enough to be handled by this TEMP_GROUP, although quite large to be handled by TEMP1 on its own.

SQL> SELECT sum(bytes)/1048576 Megs, segment_name
2 FROM dba_extents
3 WHERE segment_name = 'IDX1'
4 GROUP BY segment_name
5 /

MEGS SEGMENT_NAME
---------- -------------------------------------------
840 IDX1

What appears to be happening is that when the rebuild index has used all the space available in TEMP1 tempfile, it does not go on to use the space available in the other two tempfiles that make up the TEMP_GROUP. This seems to be contrary to the very idea of having set up a TEMP_GROUP.

This suposition is born out by the repitition of the operation using the owner of the index, whose default temp file is not TEMP_GROUP as a whole, but the component tempfile TEMP_3 which has 8048MB available

connect schema1/password

SQL> alter INDEX schema1.idx1 rebuild

Índice modificado.

-- This time the index does get rebuilt, pesumably because there is space available in TEMP_3 to carry out the rebuild.

My questions are
1. ¿Why does the original operation fail out when it has reached the limit of tempfile TEMP1 instead of using the further space availbel within TEMP_GROUP? ¿Isn´t the point of temporary tablespace groups the explicit avoidance of this type of issue?
2. Depending on the answer to #1, and asuming that the behaviour is normal (ie, that a rebuild index should not be expected to take place using more than one temp file), does anyone have any idea ¿what dicatates the order of usage of the component temp files in a group?. ¿Is it alphabetical order of tempfile name?, ¿file create time? or ¿something else?
3. As I mentioned the group was created out of existing tempfiles, rather than creating some temp file specifically to form the group. ¿Could this fact explain the inability of the operation to move onto the next temp file, once the first is exhausted. There is nothing in the documentation to suggest that there should be any difference in behaviour between a temp group created with new temp files, or the inclusion of existing temp files when creating a temp group.

As you can see, we have worked round this problem, but it is an issue of importance given that it may affect other operations that leverage this temp file group. Any information or pointers to documented instances of similar occurances would be greatly appreciated. Thank you.

Edited by: user602617 on 06-may-2009 0:57