Oracle日志报错(ORA-1652)

今天查看alert日志，发现了这样一条记录：

Sun Jun 1 18:04:17 2008
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
Sun Jun 1 18:08:54 2008
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

但事实上TEMP表空间根本就很空闲。

[@more@]

在Google上查询后发现有一个贴子是关于这个问题的讨论，觉得非常好，转贴过来。

http://www.itpub.net/viewthread.php?tid=447741&extra=&page=1

以下是原文转贴：

1楼楼主

奇怪,数据库报错：ORA-1652 .却查不出是表空间不足问题
数据库报错：ORA-1652: unable to extend temp segment by 2397 in tablespace TMPTBL

发生异常时，都是处理同一件作业。
有时候，该作业在退出后，过一段时间就恢复正常，再执行该作业也没有问题。
有时候需要重启数据库，再执行该作业也没有问题了。

曾经在发生异常时，执行
SELECT SUM(AMT1) FROM OPS$DBMST.INVOICELINE A,OPS$DBMST.INVOICEHEAD B WHERE A.CTLNO=B.CTLNO AND B.INVOICENO='05-CJ1011B-C';
这句SQL语句时，同样报错。

我查了oracle错误解释：

ORA-01652 unable to extend temp segment by string in tablespace string
Cause: Failed to allocate an extent for temporary segment in tablespace.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

但是事实上这个表空间是足够的：

rem 查看表空间使用情况
ttitle skip center "查看表空间使用情况" skip 2
select a.tablespace_name tablespace_name,a.total_mbytes total_mbytes,a.total_mbytes-b.free_mbytes used_space,
b.free_mbytes free_space,to_char(100*round(1-b.free_mbytes/a.total_mbytes,3))||'%' per
from
(select tablespace_name,round(sum(bytes)/(1024*1024),3) total_mbytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,round(sum(bytes)/(1024*1024),3) free_mbytes from user_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name and a.tablespace_name='TMPTBL';

TABLESPACE_NAME TOTAL_MBYTES USED_SPACE FREE_SPACE PER
--------------- ------------ ---------- ---------- -----------------------------------------
TMPTBL 512 84.437 427.563 16.5%

这个表空间空闲的很。
我查了alert.LOG文件，发现今几个月内出现很多次这样的类似报错：

ORA-1652: unable to extend temp segment by 2397 in tablespace TMPTBL
ORA-1652: unable to extend temp segment by 315 in tablespace TMPTBL
ORA-1652: unable to extend temp segment by 140 in tablespace TMPTBL


我查了该表空间的可用空间：
SQL> Select file_id,block_id,blocks,bytes from sys.dba_free_space where tablespace_name='TMPTBL';

FILE_ID BLOCK_ID BLOCKS BYTES
---------- ---------- ---------- ----------
10 10809 54728 448331776

查看一下表空间参数

SQL> SELECT INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,PCT_INCREASE FROM SYS.DBA_TABLESPACES WHERE TABLESPACE_NAME='TMPTBL';

INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS PCT_INCREASE
-------------- ----------- ----------- ------------
16384 16384 1 50

也没找到异常。

怎么解释这个现象呢？怎么解决这个问题呢？

2楼：会不会是quota的问题，你查一下
select * from dba_ts_quotas;

3楼：
应该是没有权限 ，grant unlimited tablesspace to xxx..

4楼：把size再扩大点,观察一下.

楼主回复：

QUOTE:

最初由 xiaohou 发布
应该是没有权限 ，grant unlimited tablesspace to xxx..

这个不可能。因为绝大部分时间是能运行这个作业。并且我手动执行过：
SELECT SUM(AMT1) FROM OPS$DBMST.INVOICELINE A,OPS$DBMST.INVOICEHEAD B WHERE A.CTLNO=B.CTLNO AND B.INVOICENO='05-CJ1011B-C';
也能执行。
只有有时候会出现不能运行状况

QUOTE: 最初由 flyerchen2000 发布
把size再扩大点,观察一下.

可是，目前空间也足够啊，512M只用了85M，还多得很啊。不大可能是表空间不够引起的呀。
因为这个库是客户的数据库。不能随便就扩，我想得找出问题原因再做处理啊。

13楼：
数据库是什么版本？
是不是8i的？
将tmptbl建成local管理的临时表空间。这个问题应该就可以解决了，如果还不行，再加大。
ps:
你这个问题一点都不奇怪。

14楼：

我们也遇到过这样的问题.你看这表空间还剩很多.就是报这个错.
我们的遇到这个错误的时候,发现,通常是sql语句要建临时表等事情,需要这个表空间的temp空间.
虽然这个表空间还有很多free,但是从dba_free_space 中可以看到,都是比较小的extent.
不满足表的next_extent的要求.修改表的next_extent.故障解决.

17楼：

1。剩余的extent太小
2。你表要求的next太大

楼主回复：

QUOTE: 最初由 flyonnet 发布 我们也遇到过这样的问题.你看这表空间还剩很多.就是报这个错.
我们的遇到这个错误的时候,发现,通常是sql语句要建临时表等事情,需要这个表空间的temp空间.
虽然这个表空间还有很多free,但是从dba_free_space 中可以看到,都是比较小的extent.
不满足表的next_extent的要求.修改表的next_extent.故障解决.

我也想到过会不会是块太小。
我查过dba_free_space 表;
SQL> Select file_id,block_id,blocks,bytes from sys.dba_free_space where tablespace_name='TMPTBL';

FILE_ID BLOCK_ID BLOCKS BYTES
---------- ---------- ---------- ----------
10 10809 54728 448331776

只有一个块，不应该存在都是小的EXTEND的原因啊。

QUOTE: 最初由 zjusxzj 发布
1。剩余的extent太小

我查过：
SQL> Select file_id,block_id,blocks,bytes from sys.dba_free_space where tablespace_name='TMPTBL';

FILE_ID BLOCK_ID BLOCKS BYTES
---------- ---------- ---------- ----------
10 10809 54728 448331776
没发现太小的块

QUOTE: 2。你表要求的next太大

为什么有时候能够执行呢？而且是大部分情况下都能执行呢？
SQL> SELECT INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,PCT_INCREASE FROM SYS.DBA_TABLESPACES WHERE TABLESPACE_NAME='TMPTBL';

INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS PCT_INCREASE
-------------- ----------- ----------- ------------
16384 16384 1 50

22楼：

QUOTE: 最初由 flyonnet 发布 我们也遇到过这样的问题.你看这表空间还剩很多.就是报这个错.
我们的遇到这个错误的时候,发现,通常是sql语句要建临时表等事情,需要这个表空间的temp空间.
虽然这个表空间还有很多free,但是从dba_free_space 中可以看到,都是比较小的extent.
不满足表的next_extent的要求.修改表的next_extent.故障解决.

呵呵，老兄是怎么从dba_free_space看出小的extent的呢？
谢谢。

我一直也搞不清楚用下边语句查出来所谓的表空间碎片
select tablespace_name,sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) fsfi
from dba_free_space
group by tablespace_name
order by 2 desc
/

对于TEMP和回滚表空间有什么意义么？

各位老大，请指点下吧

楼主回复：

Oracle数据库碎片整理

我们知道，Oracle作为一种大型数据库，广泛应用于金融、邮电、电力、民航等数据吞吐量巨大，计算机网络广泛普及的重要部门。对于系统管理员来讲，如何保证网络稳定运行，如何提高数据库性能，使其更加安全高效，就显得尤为重要。作为影响数据库性能的一大因素--数据库碎片，应当引起DBA的足够重视，及时发现并整理碎片乃是DBA一项基本维护内容。

---- 1、碎片是如何产生的

---- 当生成一个数据库时，它会分成称为表空间（Tablespace）的多个逻辑段（Segment），如系统（System）表空间,临时（Temporary）表空间等。一个表空间可以包含多个数据范围（Extent）和一个或多个自由范围块，即自由空间（Free Space）。

---- 当表空间中生成一个段时，将从表空间有效自由空间中为这个段的初始范围分配空间。在这些初始范围充满数据时，段会请求增加另一个范围。这样的扩展过程会一直继续下去，直到达到最大的范围值，或者在表空间中已经没有自由空间用于下一个范围。最理想的状态就是一个段的数据可被存在单一的一个范围中。这样，所有的数据存储时靠近段内其它数据，并且寻找数据可少用一些指针。但是一个段包含多个范围的情况是大量存在的，没有任何措施可以保证这些范围是相邻存储的，如图〈1〉。当要满足一个空间要求时，数据库不再合并相邻的自由范围（除非别无选择）， 而是寻找表空间中最大的自由范围来使用。这样将逐渐形成越来越多的离散的、分隔的、较小的自由空间，即碎片。

---- 2、碎片对系统的影响

---- 随着时间推移，基于数据库的应用系统的广泛使用，产生的碎片会越来越多，将对数据库有以下两点主要影响：

---- （1）导致系统性能减弱

---- 如上所述，当要满足一个空间要求时，数据库将首先查找当前最大的自由范围，而"最大"自由范围逐渐变小，要找到一个足够大的自由范围已变得越来越困难，从而导致表空间中的速度障碍，使数据库的空间分配愈发远离理想状态；

---- （2）浪费大量的表空间

---- 尽管有一部分自由范围（如表空间的pctincrease为非0）将会被SMON（系统监控）后台进程周期性地合并，但始终有一部分自由范围无法得以自动合并，浪费了大量的表空间。

---- 3、自由范围的碎片计算

---- 由于自由空间碎片是由几部分组成，如范围数量、最大范围尺寸等，我们可用FSFI--Free Space Fragmentation Index（自由空间碎片索引）值来直观体现：

FSFI=100*SQRT(max(extent)/sum(extents))*1/SQRT(SQRT(count(extents)))

---- 可以看出，FSFI的最大可能值为100（一个理想的单文件表空间）。随着范围的增加，FSFI值缓慢下降，而随着最大范围尺寸的减少，FSFI值会迅速下降。
---- 下面的脚本可以用来计算FSFI值：

rem FSFI Value Compute
rem fsfi.sql
column FSFI format 999,99
select tablespace_name,sqrt(max(blocks)/sum(blocks))*
(100/sqrt(sqrt(count(blocks)))) FSFI
from dba_free_space
group by tablespace_name order by 1;
spool fsfi.rep;
/
spool off;

---- 比如，在某数据库运行脚本fsfi.sql,得到以下FSFI值：
TABLESPACE_NAME FSFI
------------------------------ -------
RBS 74.06
SYSTEM 100.00
TEMP 22.82
TOOLS 75.79
USERS 100.00
USER_TOOLS 100.00
YDCX_DATA 47.34
YDCX_IDX 57.19
YDJF_DATA 33.80
YDJF_IDX 75.55

---- 统计出了数据库的FSFI值，就可以把它作为一个可比参数。在一个有着足够有效自由空间，且FSFI值超过30的表空间中，很少会遇见有效自由空间的问题。当一个空间将要接近可比参数时，就需要做碎片整理了。
---- 4、自由范围的碎片整理

---- （1）表空间的pctincrease值为非0

---- 可以将表空间的缺省存储参数pctincrease改为非0。一般将其设为1，如：

alter tablespace temp
default storage(pctincrease 1);

---- 这样SMON便会将自由范围自动合并。也可以手工合并自由范围：
alter tablespace temp coalesce;

---- 5、段的碎片整理
---- 我们知道，段由范围组成。在有些情况下，有必要对段的碎片进行整理。要查看段的有关信息，可查看数据字典dba_segments，范围的信息可查看数据字典dba_extents。如果段的碎片过多， 将其数据压缩到一个范围的最简单方法便是用正确的存储参数将这个段重建，然后将旧表中的数据插入到新表，同时删除旧表。这个过程可以用Import/Export（输入/输出）工具来完成。

---- Export（）命令有一个（压缩）标志，这个标志在读表时会引发Export确定该表所分配的物理空间量，它会向输出转储文件写入一个新的初始化存储参数--等于全部所分配空间。若这个表关闭， 则使用Import（）工具重新生成。这样，它的数据会放入一个新的、较大的初始段中。例如：

exp user/password file=exp.dmp compress=Y grants=Y indexes=Y
tables=(table1,table2);

---- 若输出成功，则从库中删除已输出的表，然后从输出转储文件中输入表：
imp user/password file=exp.dmp commit=Y buffer=64000 full=Y

---- 这种方法可用于整个数据库。

我的库查出来，如下

rem FSFI Value Compute
rem fsfi.sql
column FSFI format 999,99
select tablespace_name,sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) FSFI from dba_free_space group by tablespace_name order by 1 ;

TABLESPACE_NAME FSFI
-------------------- ----------
DBMST 22.8740216
DBMSTIND 100
EMIS 100
INDX 100
RBS 19.6950644
RBSEG 81.9410789
STREAM 83.1446822
SYSTEM 100
TEMP 100
TMPTBL 100
TOOLS 100
USERS 100

27楼：

建议把TMPTBL表空间的存储参数改一下，将pctincrease=50改为0，然后重起库清空一下临时段，重新执行那个SQL语句后再观察观察，我觉得可能和pctincrease这个参数有关，这个值等于50的话，表扩展会很厉害的，有可能是由于表的下一扩展太大而没有空余空间造成的。

28楼：

查过metalink，很多时候是SQL语句性能引起的错误。
有次也是遇到这样的错误，后来发现是程序中的一个查询编写问题。
建议检查下alert log和程序。

29楼：

QUOTE: 最初由 wxxdragon 发布 查过metalink，很多时候是SQL语句性能引起的错误。
有次也是遇到这样的错误，后来发现是程序中的一个查询编写问题。
建议检查下alert log和程序。

呵呵，这个很难定位吧，楼上能详细说下么？

30楼：

LMT,加大点你的TEMPORARY TABLESPACE

31楼：

QUOTE: 最初由 strongliq 发布
呵呵，这个很难定位吧，楼上能详细说下么？

说一下我遇到的情况。
当时也是这个错误，报表空间不能扩展，检查后却发现没有表空间不够的现象，决定很奇怪，到metalink上差，告知有时是由于不良的SQL引起的。于是检查session中SQL的运行。发现程序员写的程序中有个表连接没有写join。修改了程序后问题解决。
这样说的目的是不要一直着眼在表空间的调整上，看看其它方面。也许会有收获。
出错的语句，把执行计划帖出来看看。

32楼：

实在不行在init里面用
event="1652 trace name processstate level 10"
把执行情况给trace出来看。

楼主回复：

QUOTE: 最初由 wxxdragon 发布
说一下我遇到的情况。
当时也是这个错误，报表空间不能扩展，检查后却发现没有表空间不够的现象，决定很奇怪，到metalink上差，告知有时是由于不良的SQL引起的。于是检查session中SQL的运行。发现程序员写的程序中有个表连接没有写join。修改了程序后问题解决。
这样说的目的是不要一直着眼在表空间的调整上，看看其它方面。也许会有收获。
出错的语句，把执行计划帖出来看看。

你说的非常对！有些时候有的问题可能影响到其他问题！
但是，这个系统不是我们开发的。我们是维护他的数据库。程序我们看不到也没办法去查程序。所以很麻烦啦

35楼：

加大temp空间，如你没有用lmt，可以考虑手工回缩整理下空间。
你的问题仅仅是排序峰值引起的。可以考虑看看sql，是否有调整空间。

37楼：

１．chenfeng 已经说的很对了
２．你看tablespace的init,nect,pct是不对的，应该从dba_tables查看表一级的设置

39楼：

搜索至此,贴出另外一种可能性.

Subject: Bug 2858082 - False ORA-1652 in alert log when there is free space in RAC environment
Doc ID: Note:2858082.8 Type: PATCH
Last Revision Date: 10-AUG-2005 Status: PUBLISHED
Click here for details of sections in this note.

Bug 2858082 False ORA-1652 in alert log when there is free space in RAC environment
This note gives a brief overview of bug 2858082.

Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 10.2
Versions confirmed as being affected 10.1.0.3
9.2.0.2

Platforms affected Generic (all / most platforms affected)

Fixed:
This issue is fixed in 9.2.0.7 (Server Patch Set)
10.1.0.4 (Server Patch Set)
10.2.0.1 (Base Release)

Symptoms: Related To:
Error May Occur
ORA-1652
RAC (Real Application Clusters) / OPS
Space Management

Description
In the RAC environment an ORA-1652 can be reported to the alert.log
even if there are free extents are available and the user session
does get an extent without error to the session.

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

转载于:http://blog.itpub.net/266238/viewspace-1005047/