ORA-1652: unable to extend temp segment by 8192 in tablespace XXX

   用户在运行以下语句时报ORA-1652

1. Insert into TMP_FACT_XX01
   
2.   (
   
3.     CONTNO,
   
4.     POLNO,
   
5.     MAINPOLYEAR
   
6.   )
   
7.   SELECT /*+parallel(a, 8)*/
   
8.           MAX(CONTNO),
   
9.           POLNO,
   
10.           MAINPOLYEAR
    
11.   FROM FACT_XX01 a
    
12.   GROUP BY POLNO, MAINPOLYEAR;

   报错信息如下：

1. ORA-1652: unable to extend temp segment by 128 in tablespace XXX01

   注意这里的XXX01是FACT_XX01所在表空间，并非temp表空间，所以这里的ORA-1652并非是group by引起，而且在进行insert into ...  select ... 时需要在insert into的表空间中产生一个临时段用于存储select查询产生的结果集，待语句执行结束后，这个临时段会变成永久段，就是insert into的表段。会产生这种临时段的操作还很多，例如：create index、create pk constraint、enable constraint、CATS等。
  下面查看下XXX01表空间free空间大小：

1. select sum(bytes)/1024/1024/1024 from dba_free_space where tablespace_name = 'XXX01';
2. SUM(BYTES)/1024/1024/1024
   
3. 108.329162597656
   

    FACT_XX01表大小为23.53GB，所以 XXX01表空间是足以放下上述sql中select部分产生的结果集的，因为sql中还有group by，最后的结果集肯定小于23.53GB，但是为什么还会报上面的错误？
  这里要注意，上面的查询只是看总的剩余空间是否足够，但是表空间是会存在碎片的，也就是说上面看到的free空间可能是由很多不连续的空间组成的，而这里的临时段需要连续的空间，接下来再观察表空间中最大的连续free空间：

1. select max(bytes)/1024/1024/1024 from dba_free_space where tablespace_name='XXX01';
   
2. MAX(BYTES)/1024/1024/1024
   
3. 3.875

    这里最大的连续free空间只有3.875GB，所以不足以放下上述报错sql的select结果集，导致出现ORA-1652。
 所以，要解决这个错误，可以整理表空间碎片，也可以为表空间增加新的空间。
 另外，表空间的碎片情况，可以用下面的语句进行查询：

1. select
   
2.   total.tablespace_name tsname,
   
3.   count(free.bytes) nfrags,
   
4.   nvl(max(free.bytes)/1024,0) mxfrag,
   
5.   total.bytes/1024 totsiz,
   
6.   nvl(sum(free.bytes)/1024,0) avasiz,
   
7.   (1-nvl(sum(free.bytes),0)/total.bytes)*100 pctusd
   
8. from
   
9.   dba_data_files total,
   
10.   dba_free_space free
    
11. where
    
12.   total.tablespace_name = free.tablespace_name(+)
    
13.   and total.file_id=free.file_id(+)
    
14. group by
    
15.   total.tablespace_name,
    
16.   total.bytes
    
17. /

   后记：当然，一开始的insert into ... select ...语句是有问题的，用户的本意是使用并行提高速度，但是语句只在select部分开启了并行，insert部分没有开启并行，而且DML的并行是需要单独设置，不能光使用hint。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29821678/viewspace-1596125/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29821678/viewspace-1596125/