参考文档: <的研究与测试_20091130>>

临时表空间的申请与释放问题学习

1 临时表空间的申请与释放的理解

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

3 监控临时表空间

4 结论

5 Notes

临时表空间有三种类型，这里所讨论的都是以Temporary tablespace为参考的，另外两种情况不再深入学习，只要知道有这回事。

1 临时表空间的申请与释放的理解

数据库刚刚startup 后，第一个需要进行disk sort的操作会在Temporary Tablespace上创建一个Temporay Segment. Temporary Segment 在Temporary tablespace 中也称为Sort Segment. 由sys拥有，而不是执行排序操作的用户。通常每个临时表空间中只有一个排序段，因为多个会话可以共享排序段，用户使用临时表空间不需要在其上有quota，事实上会被Oracle忽略。当语句结束时，分配给这个sort segment并没有被相应的回收，而是由其它会话共享这个sort segment. 只有shutdown时才被释放。

All processes performing sorts reuse existing sort extents of the sort segment, rather than allocating a segment (and potentially many extents) for each sort. If an insufficient number of extents exist for the number of sorts currently in operation, then the required extents are added once for each instance startup. They are recycled thereafter.<<官方的说明>

如果发生以下情况排序操作将会失败：

排序段中没有不再使用的块；

临时表空间中没有空间可以供排序段分配额外的分区。

这在大多数情况下会导致语句发生以下错误：

“ORA-1652: unable to extend temp segment.”并记录在实例的alert log中。

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

2 识别由于缺少临时表空间失败的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进行会话级跟踪。

3 监控临时表空间

查看临时表空间的使用情况(会重用,只有不够时才又重新请求分配)V$TEMP_SPACE_HEADER

select TABLESPACE_NAME,file_id,

(bytes_used+bytes_free)/1024/1024 "size_total(mb)",

bytes_used/1024/1024 "size_allocated(mb)",

bytes_free/1024/1024 "size_unallocated(mb)"

from V$TEMP_SPACE_HEADER;

查看正在使用的sort_segment,V$SORT_SEGMENT

select tablespace_name, CURRENT_USERS,TOTAL_EXTENTS,USED_EXTENTS,FREE_EXTENTS

from v$sort_segment;

查看哪个用户的哪个语句在使用sort_segment,V$SORT_USAGE=v$tempseg_usage

select a.username, a.SESSION_NUM,b.sql_text, a.TABLESPACE

from v$tempseg_usage a, v$sqlarea b

where a.SQLHASH = b.hash_value;

4 结论

关于临时表空间的重用与共享：

假定用户test使用temp作为临时表空间，临时表空间大小2G，当test用户发起的一次操作需要的临时表空间超过2G时(当然这里可能没到2G就报错了)，就会报类似错误。说明临时表空间不够用。

ORA-01652: unable to extend temp segment by 2048 in tablespace TEMP_TEST

如不超过2G，则没问题。下一次的操作又重新使用所有空间。

而通过

select TABLESPACE_NAME,file_id, bytes_used/1024/1024 "size used(mb)", bytes_free/1024/1024 "size free(mb)" from V$TEMP_SPACE_HEADER;

看到的是已分配的空间，但不是已使用的空间。

假定多个用户共用temp作为临时表空间，当几个用户同时发起排序动作，则一旦所需的临时表空间总和超过2G，同样报

ORA-01652: unable to extend temp segment by 2048 in tablespace TEMP_TEST

.

所有的会话共享sort segment里面的extents，一个会话从sort segment中得到合适的extent个数，当会话完成后，分配给会话的的extents被回收到sort segment.

不回收的是sort segment,而回收的是extents.（这里理解可能有问题）

5 Notes

1 Temporary segment 与 sort segment是包含的关系。

2 做测试时要有一个干净的环境，不能与其它应用共享，影响结果

ITPub博客

临时表空间的申请与释放问题学习_20091203

最新文章