欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-03-17 19:08:35
...

用户在运行以下语句时报ORA-1652,报错信息如下:ORA-1652: unable to extend temp segment by 128 in tablespace XXX01。注意这

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

Insert into TMP_FACT_XX01

(

CONTNO,

POLNO,

MAINPOLYEAR

)

SELECT /*+parallel(a, 8)*/

MAX(CONTNO),

POLNO,

MAINPOLYEAR

FROM FACT_XX01 a

GROUP BY POLNO, MAINPOLYEAR;

报错信息如下:

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空间大小:

select sum(bytes)/1024/1024/1024 from dba_free_space where tablespace_name = 'XXX01';
SUM(BYTES)/1024/1024/1024

108.329162597656

FACT_XX01表大小为23.53GB,所以XXX01表空间是足以放下上述sql中select部分产生的结果集的,因为sql中还有group by,最后的结果集肯定小于23.53GB,但是为什么还会报上面的错误?

这里要注意,,上面的查询只是看总的剩余空间是否足够,但是表空间是会存在碎片的,也就是说上面看到的free空间可能是由很多不连续的空间组成的,而这里的临时段需要连续的空间,接下来再观察表空间中最大的连续free空间:

select max(bytes)/1024/1024/1024 from dba_free_space where tablespace_name='XXX01';

MAX(BYTES)/1024/1024/1024

3.875

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

select

total.tablespace_name tsname,

count(free.bytes) nfrags,

nvl(max(free.bytes)/1024,0) mxfrag,

total.bytes/1024 totsiz,

nvl(sum(free.bytes)/1024,0) avasiz,

(1-nvl(sum(free.bytes),0)/total.bytes)*100 pctusd

from

dba_data_files total,

dba_free_space free

where

total.tablespace_name = free.tablespace_name(+)

and total.file_id=free.file_id(+)

group by

total.tablespace_name,

total.bytes

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