【赛迪网－it技术报道】在实际的应用中，很多人经常会遇到“ora-1652: unable to extend temp segment”临时表空间被资源中的多个会话共享的情况，并且quotas不能限制每个用户使用的临时表空间数量，当临时表空间被填充满时，任何尝试获得更多的临时表空间的用户将会得到“ora-1652: unable to extend temp segment”错误。

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 2 17:21:14 2007

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;