ORA-01652: unable to extend temp segment by 128 in tablespace TEMP / ORACLE表空间的碎片整理

-- ORA-01652: unable to extend temp segment by 128 in tablespace TEMP 

Example1:

ORA-01652:unable to extend temp segment by (number) in tablespace (tablespace_name)

　　产生原因：ORACLE临时段表空间不足，因为ORACLE总是尽量分配连续空间，一旦没有足够的可分配空间或者分配不连续就会出现上述的现象。

　　解决方法：我们知道由于ORACLE将表空间作为逻辑结构单元，而表空间的物理结构是数据文件，数据文件在磁盘上物理地创建，表空间的所有对象也存在于磁盘上。为了给表空间增加空间，就必须增加数据文件。先查看一下指定表空间的可用空间，使用视图SYS.DBA_FREE_SPACE，视图中每条记录代表可用空间的碎片大小：

SQL>Select file_id,block_id,blocks,bytes from sys.dba_free_space where tablespace_name = '(tablespace_name)';

　　返回的信息可初步确定可用空间的最大块，看一下它是否小于错误信息中提到的尺寸，再查看一下缺省的表空间参数：

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

　　通过下面的SQL命令修改临时段表空间的缺省存储值：

SQL>ALTER TABLESPACE (tablespace_name) DEFAULT STORAGE (INITIAL XXX NEXT YYY);

　　适当增大缺省值的大小有可能解决出现的错误问题，也可以通过修改用户的临时表空间大小来解决这个问题：

SQL>ALTER USER (username) TEMPORARY TABLESPACE (new_tablespace_name);

　　使用ALTER TABLESPACE命令，一旦完成，所增加的空间就可使用，无需退出数据库或使表空间脱机，但要注意，一旦添加了数据文件，就不能再删除它，若要删除，就要删除表空间。

　　一个报错例子如下：

ORA-1652:unable to extend temp segment by 128 in tablespace REP_TSHEA

Example2:

“ORA-1652: unable to extend temp segment”

      临时表空间被资源中的多个会话共享，并且quotas不能限制每个用户使用的临时表空间数量，当临时表空间被填充满时，任何尝试获得更多的临时表空间的用户将会得到“ORA-1652: unable to extend temp segment”错误。

 

解决办法：
1、扩大临时tablespace
2、调整sql语句或者创建index等，减少临时tablespace的使用

 

Oracle排序的基础

      Oracle会话首先在内存中进行排序，当Oracle需要存储数据到临时表或者为哈西排序建立哈希表时，并且也会首先在内存中进行操作，虽然这两个操作不需要排序操作，但是它们在Oracle中的处理方式是相同的。

      如果操作使用内存超过了阈值，Oracle会将操作分为多个较小的操作以使每个可以在内存中操作。部分结果将会被写入磁盘的临时表空间，任何一个会话可以使用的内存数依赖于初始化参数的设置，如果workarea_size_policy为auto，则由pga_aggregate_target控制，否则由sort_area_size, hash_area_size，和bitmap_merge_area_size控制内存的使用。

      当排序操作太大以至于不能在内存中执行时，Oracle将在临时表空间中分配空间以执行操作。临时段在临时表空间中—也称为“排序段”，sys拥有，而不是执行排序操作的用户。通常每个表空间中只有一个排序段，因为多个会话可以共享排序段，用户使用临时表空间不需要在其上有quota，事实上会被Oracle忽略。

      临时表空间中只能包含临时段，因此临时段上的操作不会产生undo和redo，同时分配临时段给用户也不需要记录在dd或位图块上。因为临时表空间不会超过创建它的会话的生命周期。

      一个SQL可以有多个排序操作，一个数据库会话同时可以有多个活动的SQL，当到磁盘上的排序结果不再需要时，其在排序段中的块会标记为不再使用并可以被分配给其他排序操作。

      如果发生以下情况排序操作将会失败：排序段中没有不再使用的块；临时表空间中没有空间可以供排序段分配额外的分区。这在大多数情况下会导致语句发生以下错误：“ORA-1652: unable to extend temp segment.”并记录在实例的alert log中。

      不过需要注意的是ORA-1652并不全部指示临时表空间问题，ALTER TABLE…MOVE也会发生该错误，如果目标表空间没有足够的空间容纳移动的表空间。

 

识别由于缺少临时表空间失败的SQL语句

      虽然Oracle logs ORA-1652错误到警告日志中通知dba发生了空间问题，但是Oracle不会识别那条错误的语句。

      可以使用Oracle诊断事件跟踪ORA-1652事件，该诊断事件的影响很小，仅在发生ORA-1652错误时才会写入信息。

      ALTER SESSION SET EVENTS '1652 trace name errorstack';

      在会话范围内设置；

      ALTER SYSTEM SET EVENTS '1652 trace name errorstack';

      永久性设置：

      ALTER SYSTEM SET EVENT = '1652 trace name errorstack' SCOPE = SPFILE;

      还可以在其他会话内使用“oradebug event”进行跟踪。

 

      可以使用以下语句关闭：

ALTER SYSTEM RESET EVENT SCOPE = SPFILE SID = '*';

ALTER SYSTEM SET EVENTS '1652 trace name context off';

ALTER SESSION SET EVENTS '1652 trace name context off';

如果一个SQL语句由于缺少临时表空间失败并且ORA-1652诊断事件已经激活，那么

Oracle服务器进程将会在遇到错误时在user_dump_dest目录的跟踪文件写入错误信息，并且警告日志会指示出相关跟踪文件。如：

Tue Jan 217:21:142007

Errors in file

/u01/app/oracle/admin/rpkprod/udump/rpkprod_ora_10847.trc: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

      跟踪文件中将包含类似如下的信息：

Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_2

System name:   SunOS

Node name:     rpk

Release:       5.8

Version:       Generic_108528-27

Machine:       sun4u

Instance name: rpkprod

Redo thread mounted by this instance: 1

Oracle process number: 18

Unix process pid: 10847, image: oracle@rpk (TNS V1-V3)

 

*** ACTION NAME:() 2007-01-02 17:21:14.871

*** MODULE NAME:(SQL*Plus) 2007-01-02 17:21:14.871

*** SERVICE NAME:(SYS$USERS) 2007-01-02 17:21:14.871

*** SESSION ID:(130.13512) 2007-01-02 17:21:14.871

*** 2007-01-02 17:21:14.871

ksedmp: internal or fatal error

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Current SQL statement for this session:

SELECT  "A1"."INVOICE_ID", "A1"."INVOICE_NUMBER", "A1"."INVOICE_DAT

E", "A1"."CUSTOMER_ID", "A1"."CUSTOMER_NAME", "A1"."INVOICE_AMOUNT",

 "A1"."PAYMENT_TERMS", "A1"."OPEN_STATUS", "A1"."GL_DATE", "A1"."ITE

M_COUNT", "A1"."PAYMENTS_TOTAL"

FROM    "INVOICE_SUMMARY_VIEW" "A1"

ORDER BY "A1"."CUSTOMER_NAME", "A1"."INVOICE_NUMBER"

----- Call Stack Trace -----

虽然使用这种方法可以得到相当详细的信息，但是需要注意的是，这种方法捕获到的语

句并不一定是问题的根源，因为有可能前一个语句消耗了99.9%临时空间，而第二个语句被捕获到跟踪文件中。

      跟踪文件同时还会包含如调用栈跟踪和二进制栈dump，该信息通常没有价值，除非想要了解Oracle内部。

      通常不应该在实例级别设置该诊断事件。如果经常在批处理期间遇到该错误，可以在批处理开始设置alter session进行会话级跟踪。

 

监控临时表空间

      可以在发生错误前实时监控数据库中临时表空间的使用情况，以避免出现错误。任何时候，Oracle都可以告诉dba数据库中的临时表空间，会话使用的排序空间，以及语句使用的排序空间。所有这些信息都可以通过v$得到。

临时段

      Oracle会在第一次执行磁盘排序时创建排序段，并且根据需要扩展，但是不会收缩。

SELECT A.tablespace_name tablespace,
      D.mb_total,
      SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
      D.mb_total - SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_free
 FROM v$sort_segment A,
      (SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total
         FROM v$tablespace B, v$tempfile C
        WHERE B.ts# = C.ts#
        GROUP BY B.name, C.block_size) D
 WHERE A.tablespace_name = D.name
 GROUP by A.tablespace_name, D.mb_total;

 

会话使用的排序空间

SELECT S.sid || ',' || S.serial# sid_serial,
      S.username,
      S.osuser,
      P.spid,
      S.module,
      S.program,
      SUM(T.blocks) * TBS.block_size / 1024 / 1024 mb_used,
      T.tablespace,
      COUNT(*) sort_ops
 FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
 WHERE T.session_addr = S.saddr
  AND S.paddr = P.addr
  AND T.tablespace = TBS.tablespace_name
 GROUP BY S.sid,
         S.serial#,
         S.username,
         S.osuser,
         P.spid,
         S.module,
         S.program,
         TBS.block_size,
         T.tablespace
 ORDER BY sid_serial;

 

语句使用的临时空间

SELECT S.sid || ',' || S.serial# sid_serial,
      S.username,
      T.blocks * TBS.block_size / 1024 / 1024 mb_used,
      T.tablespace,
      T.sqladdr address,
      Q.hash_value,
      Q.sql_text
 FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
 WHERE T.session_addr = S.saddr
  AND T.sqladdr = Q.address(+)
  AND T.tablespace = TBS.tablespace_name
 ORDER BY S.sid;

--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

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

转自：http://blog.itpub.net/post/69/1906