$ oerr ora 1652
01652, 00000, "unable to extend temp segment by %s in tablespace %s"
// *Cause: Failed to allocate an extent of the required number of blocks for
//          a temporary segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
//          files to the tablespace indicated.

许多人第一感觉总认为是临时表空间不足，要增大临时表空间，实际上还应该考虑其他因素。实际上当建立索引时时也要在永久表空间上建立临时段，当然如果你排序很大也需要在临时表空间建立。当索引建立完成后，oracle会改变永久表空间的临时段进入永久段。另外使用ctas建立表时情况也是一样的。

例子说明：

1.准备测试：建立用户test,表空间test 2M,AUTOEXTEND=off.

CREATE TABLESPACE test DATAFILE SIZE 2M AUTOEXTEND OFF LOGGING PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
CREATE USER TEST IDENTIFIED BY DEFAULT TABLESPACE TEST TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;
ALTER USER TEST QUOTA UNLIMITED ON TEST;

2.
SQL> create table a1 as select rownum id ,'test' name from dual connect by level <=100000;
create table a1 as select rownum id ,'test' name from dual connect by level <=100000
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEST
--注意提示的是TEST表空间temp segment无法扩展！

SQL> create table a1 as select rownum id ,'test' name from dual connect by level <=50000;
Table created.

SQL> col total_bytes format 999999999
SQL> col free_bytes format 999999999
SQL> col file_name format a40

SQL>Select MAX(d.bytes) total_bytes,
       nvl(SUM(f.Bytes), 0) free_bytes,
       d.file_name,
       MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
       ROUND(SQRT(MAX(f.BLOCKS)/SUM(f.BLOCKS))*(100/SQRT(SQRT(COUNT(f.BLOCKS)) )), 2) frag_idx
from   DBA_FREE_SPACE f , DBA_DATA_FILES d
where f.tablespace_name(+) = d.tablespace_name
and    f.file_id(+) = d.file_id
and    d.tablespace_name = 'TEST'
group by d.file_name

TOTAL_BYTES FREE_BYTES FILE_NAME                                USED_BYTES   FRAG_IDX
----------- ---------- ---------------------------------------- ---------- ----------
    2097152     131072 +DATA/test/datafile/test.284.770380219      1966080        100

还剩下131072字节。

3.再建立立索引：
SQL> create unique index i_a1_id on a1(id);
create unique index i_a1_id on a1(id)
                               *
ERROR at line 1:
ORA-01652: unable to extend temp segment by 8 in tablespace TEST

4.加大test表空间大小。
ALTER DATABASE DATAFILE '+DATA/test/datafile/test.284.770380219' RESIZE 10M;

再建立索引：

SQL> create unique index i_a1_id on a1(id);
Index created.

    可以发现问题在于test表空间无法扩展，而并非临时表空间不足！

ITPub博客

[20111220]关于ora-01652.txt

最新文章