Space Errors (ORA-1652) when using Multiple Temporary Tablespaces with a Serial Transaction (Doc ID 248712.1)

Last updated on OCTOBER 19, 2023

Applies to:

Symptoms

You have multiple temporary tablespaces defined and you are still experiencing
space management errors. With more than one temporary tablespace, a single SQL 
operation does not use more than one temporary tablespace for sorting.

You may see errors similar to 
 SQL>   create index i1 on test_temp(tname);
        create index i1 on test_temp(tname)
                     *
 ERROR at line 1:
 ORA-01652: unable to extend temp segment by 128 in tablespace ZUL02

Sorting does not span across multiple temporary tablespace in a tablespace 
group for a user.

Changes

SQL> create temporary tablespace zul tempfile 
   'zul_temp01.db' size 2m
     tablespace group zulg;

SQL> create temporary tablespace zul2 tempfile
  'zul_temp02.db' size 2m
  tablespace group zulg;

SQL> select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
ZULG                           ZUL
ZULG                           ZUL2

SQL> create user zul
  2  identified by zul
  3  default tablespace users
  4  temporary tablespace zulg;

SQL>  grant create session,connect to zul;
SQL>  alter user zul quota unlimited on users;
SQL>  alter user zul quota unlimited on ZUL;
SQL>  alter user zul quota unlimited on ZUL2;

Connect as zul/zul
SQL>   create index i1 on test_temp(tname);
  create index i1 on test_temp(tname)
                     *
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace ZUL02

While the sort is going on check v$sort_usage,v$sort_segment


SQL> SELECT   b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
           a.username
  FROM     v$session a,v$sort_usage b
  WHERE    a.saddr = b.session_addr
  ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
  

TABLESPACE                        SEGFILE#    SEGBLK#     BLOCKS        SID    
SERIAL# USERNAME                                       
------------------------------- ---------- ---------- ---------- ---------- 
---------- ---------
ZUL02                                  203       9481        128         18    
   2092 ZUL                                           

SQL>  SELECT tablespace_name, extent_size, total_extents, used_extents,
  2           free_extents, max_used_size
  3        FROM v$sort_segment;

TABLESPACE_NAME                 EXTENT_SIZE TOTAL_EXTENTS USED_EXTENTS 
FREE_EXTENTS MAX_USED_SIZE
------------------------------- ----------- ------------- ------------ 
------------ -------------
TEMP                                    128             0            0         
   0             0
ZUL02                                   128            99           54         
  45            99

From the above selects, you can see that only ZUL2 tablespace is being used.

Cause