temp 表空间无法扩展案例分析

2021-10-03 21:24:51

http://happay99.blog.hexun.com/40831530_d.html

解决ora-01652无法通过128（在temp表空间中）扩展temp段的过程

执行一个sql语句后，大约花了10分钟，好不容易有一个结果，但是报了一个ora-01652错误，查阅了oracle的错误代码说明：意思是指temp表空间无法自动扩展temp段。这种问题一般有两种原因：一是临时表空间空间太小，二是不能自动扩展。

分析过程：

既然是temp表空间有问题，那当然就要从temp表空间说起啦。首先要说明的是temp表空间的作用，temp表空间主要是用作需要排序的操作。

   1.临时表空间是用于在进行排序操作(如大型查询,创建索引和联合查询期间存储临时数据)每个用户都有一个临时表空间。
   2.对于大型操作频繁,(大型查询,大型分类查询,大型统计分析等),应指定单独的临时表空间,以方便管理。
   3.分配用户单独临时表空间,一般是针对大型产品数据库,OLTP数据库,数据库仓库对于小型产品不需要单独制定临时表空间,使用默认临时表空间。
   正常情况下，一个sql执行之后，返回结果后系统会自动收回分配给这个用户的空间。以便可以把此部分空间再分配给其他用户。

到底什么时候需要分配临时段呢？比如说执行一个sql语句需要存储中间结果，或者这个sql语句需要排序操作（如果小的话可以直接在PGA的sort_area_size里完成，大了就要用到这里专门准备的临时段排序。如果用索引进行排序的话也不用分配这个临时段）。

下面这些语句执行过程中可能用到临时段：

CREATE INDEX
SELECT ... ORDER BY
SELECT DISTINCT ...
SELECT ... GROUP BY
SELECT . . . UNION
SELECT ... INTERSECT
SELECT ... MINUS

有些不能使用索引的关联操作（unindexed join），或者需要在子查询间建立相互关系（correlated subqueries），也可能需要使用临时段（temporary segment）。所以当查询包含DISTINCT，GROUP BY，或 ORDER BY 子句时，Oracle有可能使用两个临时段。

oracle为其临时表及其索引分配临时段，临时表中的数据只有在会话或者事务运行期间才存在。。我估计就和视图差不多。但是也不对，这个貌似是用到的时候才分配临时表，而视图是提前定义好了在数据字典中的。

当用户首次使用 INSERT 语句向一个临时表（temporary table）插入数据时，Oracle为这个临时表创建临时段（这个插入操作也可能是隐式地由 CREATE TABLE AS SELECT 语句执行）。当首个 INSERT 执行时，Oracle为临时表及其索引分配临时段，创建索引的root page，同时创建所有的 LOB 段（LOB segment）。

临时表（temporary table）的段存储在创建者的临时表空间（temporary tablespace）中。

当事务（transaction）结束时，Oracle负责移除（drop）属于此事务的临时表（transaction-specific temporary table）使用的段，同样地，当会话（session）结束时，Oracle负责移除（drop）属于此会话的临时表（session-specific temporary table）使用的段。但是如果其他会话或事务也在使用此临时表，此段将继续保存

Temp表空间满时的处理方法：

一、修改参数(仅适用于8i及8i以下版本)

修改一下TEMP表空间的storage参数，让Smon进程观注一下临时段，从而达到清理和TEMP表空间的目的。

SQL>alter tablespace temp increase 1;
SQL>alter tablespace temp increase 0;

二、kill session

1、使用如下语句a查看一下认谁在用临时段

SELECT se.username, se.SID, se.serial#, se.sql_address, se.machine, se.program, su.TABLESPACE,
su.segtype, su.CONTENTS FROM v$session se, v$sort_usage su
WHERE se.saddr = su.session_addr

2、kill正在使用临时段的进程

SQL>Alter system kill session 'sid,serial#';

3、把TEMP表空间回缩一下

SQL>Alter tablespace TEMP coalesce;

注：

这处方法只能针对字典管理表空间(Dictionary Managed Tablespace)。于本地管理表空间（LMT:Local Managed Tablespace），不需要整理的。9i以后只能创建本地管理的表空间。

CREATE TABLESPACE TEST DATAFILE 'D:\TEST01.dbf' SIZE 5M EXTENT MANAGEMENT DICTIONARY
CREATE TABLESPACE TEST DATAFILE 'D:\TEST01.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL;

三、重启数据库库

库重启时，Smon进程会完成临时段释放,TEMP表空间的清理操作，不过很多的时侯我们的库是不允许down的,所以这种方法缺少了一点的应用机会，不过这种方法还是很好用的。

四、使用诊断事件的一种方法,也是最有效的一种方法

1、确定TEMP表空间的ts#

SQL>select ts#, name from sys.ts$ ;
TS# NAME
---------- ------------------------------
0 SYSTEM
1 UNDOTBS1
2 SYSAUX
3 TEMP
4 USERS
5 UNDOTBS2

2、执行清理操作

SQL>alter session set events 'immediate trace name DROP_SEGMENTS level 4' ;

说明：

temp表空间的TS# 为 3, So TS#+ 1= 4

重建TEMP 表空间：

Temporary tablespace是不能直接drop默认的临时表空间的，不过我们可以通过以下方法来做。

准备：查看目前的Temporary Tablespace

SQL> select name from v$tempfile;
NAME
———————————————————————
D:\ORACLE\ORADATA\TEST\TEMP01.DBF
SQL> select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW TEMP
SYS TEMP
SYSTEM TEMP
DBSNMP TEMP
SYSMAN TEMP

1.创建中转临时表空间

create temporary tablespace TEMP1 TEMPFILE 'E:\ORACLE\ORADATA\ORCL\temp02.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

2.改变缺省临时表空间为刚刚创建的新临时表空间temp1

alter database default temporary tablespace temp1;

3.删除原来临时表空间

drop tablespace temp including contents and datafiles;

4.重新创建临时表空间

create temporary tablespace TEMP TEMPFILE 'E:\ORACLE\ORADATA\ORCL\temp01.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

5.重置缺省临时表空间为新建的temp表空间

alter database default temporary tablespace temp;

6.删除中转用临时表空间

drop tablespace temp1 including contents and datafiles;

7.如果有必要，那么重新指定用户表空间为重建的临时表空间

alter user arbor temporary tablespace temp;

查看表空间语句，不过查不出Temp表空间：

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),
'990.99') "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC

以上就是Oracle临时表空间的处理方法，希望会对读者带来帮助。

好了，既然问题出在临时表空间，那就查看一下临时表空间信息：

   select * from dba_tablespaces;
   select * from dba_temp_files;
   select * from v$tempfile

通过观察发现，temp表空间只有一个数据文件，大小为4G，不自动扩展。大小为4G，应该说是不能算小啦。这时想到，需要看一看执行的sql语句到底是什么样的，难道它能把这个空间用完（由于系统事务非常少，所以暂不考虑其它用户语句对temp的影响），于是就把那个语句拷贝下拉，研究一番：

SELECT a.TASKID, a.EXAMTYPECODE, a.CONTROLDEPARTMENT, a.DEFAULTRECRUITSPEC, a.ISUPCUNTRYLINE,
a.ISUPCOLLEGELINE, a.ISMAYJOINRETRIAL, a.JOINRETRIALGIST, a.JOINRETRIALBAK, a.TEMPERSIGN,
a.RECRUITSTUDENTTYPE, a.MATRICULATEGIST, a.ISNOTICEEXAMINEE, a.AFTERHANDLEDEP, a.TUITION,
a.KSBH, a.BH, a.XXB, a.BMDDM, a.BMDMC,
a.BMH, a.XM, a.XMPY, a.ZJLX, a.ZJLXMC,
a.ZJHM, a.CSRQ, a.MZM, a.MZ, a.XBM,
a.XB, a.HFM, a.HF, a.ZZMMM, a.ZZMM,
a.HKSZSSM, a.HKSZSSMC, a.HKSZDXXDZ, a.CSDSSM, a.CSDSSMC,
a.CSDXXDZ, a.XXGZDW, a.TXDZ, a.YZBM, a.LXDH,
a.DZXX, a.BYNY, a.XLM, a.XL, a.XWM,
a.XW, a.KSLYM, a.KSLY, a.KSFSM, a.KSFS,
a.BKLBM, a.BKLB, a.DASZDWSSM, a.DASZDWSSMC, a.DASZDW,
a.DASZDWDZ, a.DASZDWYZBM, a.XXGZJL, a.BYXX, a.BYXX1,
a.BYXX2, a.BYXX3, a.BYXX4, a.JL, a.JTCY,
a.BYDW, a.BYDWM, a.BYZYDM, a.BYZYMC, '0'||a.DWDM DWDM,
a.DWMC, a.TJDWDM, a.TJDWMC, a.ZYDM, a.ZYMC,
a.YXSM, a.YXSMC, a.YJFXM, a.YJFX, a.DSXM,
a.ZZLLM, a.ZZLLMC, a.WGYM, a.WGYMC, a.YWK1M,
a.YWK1MC, a.YWK2M, a.YWK2MC, a.YWK3M, a.YWK3MC,
a.XYJRM, a.XYJR, a.JFBZ, a.ZXBZ, a.BMSJ,
a.BYXXBZ, a.SFZH, a.BYND, a.KSDW, a.DWSZSSM,
a.DWSZSSMC, a.ZZLL, a.WGY, a.YWK1, a.YWK2,
a.YWK3, a.ZF, (NVL(a.fscj,0)) FSCJ, a.LQLBM, a.LQLB,
a.DXWPDW, a.PG, a.PGBZ, a.BZ, a.WDDWLM,
a.WDDWL, a.JFLY, a.LQQK, a.BLDW, a.NRXNY,
a.LQND, a.BZ1, a.BZ2, a.KCH, a.KCDD,
a.ZWH,a.ZPPATH, a.XH, a.BKZYM, a.BKZY,
a.XVH,a.DY6FLAG, a.BXYWK1MC, a.BXYWK2MC, a.BXYWK3MC,
a.QKBZ1, a.QKBZ2, a.QKBZ3, a.QKBZ4, a.QKBZ5,
a.SSMZLQ, a.KSQK, a.JGM, a.JGS, a.JGSX,
a.NLDM, a.NL, a.DXZYM, a.DXZY, a.HKSZDSX,
a.DQDM, a.YDWYB, a.BRTXZ, a.GZDWZ, a.YDWLM,
a.DWLB, a.BRYB, a.BRDH, a.JTDWM, a.GL,
a.TSLB, a.TSLBM, a.XXFSM, a.XXFS, a.LQZYM,
LQZY, ZPCJ, DSXHMH1, DSXHMH2, DSXHMH3,
a.DSXHMH4, a.SZSSM, a.ZXJH, a.ZXJHBZ, a.BLZGNX,
BLZG, BLZG1, BLZBZ, JS1MC, JS1CJ,
a.JS2MC, a.JS2CJ, a.SSMZ, a.SSMZBZ, a.DXWPSSM,
a.DXWPSS, a.LQXSM, a.LQXSMC, a.LQDSM, a.LQDSXM,
a.LQYJFXM, a.LQYJFXMC, a.HANDLERESULT, a.XLZSBH, a.XWZSBH,
a.LQYJFXID, a.XXXS,nvl2(a.reexam , ROUND(GYZF),(zf * ( 1- 0.3))) GYZF,a.GYFS, a.GYCS,
b.reexam ,a.SPECIALITYNO,a.SPECIALITYNAME,
   (
   SELECT STUDENTTYPE FROM CODE_STUDENTTYPE
   WHERE STUDENTTYPECODE=A.recruitstudenttype ) STUDENTTYPE,
   (SELECT collegename FROM ENROL_COLLEGEINFO) FSDWMC,
   (SELECT collegecode FROM ENROL_COLLEGEINFO) FSDWDM,
   (CASE WHEN a.handleresult='已录取' THEN '1' ELSE '0' END ) nlq,'' HKSZDM
FROM
( SELECT X.*, Y.SPECIALITYNO,Y.SPECIALITYNAME FROM ENROL_EXAMINEE X,   BASE_SPECIALITY Y WHERE X.DEFAULTRECRUITSPEC=Y.SPECSTANDCODE AND x.CONTROLDEPARTMENT IS NOT NULL ) A
,(SELECT taskid,reexam FROM ENROL_EXAMINEE) b WHERE a.taskid=b.taskid
ORDER BY A.KSBH ASC

咋一看，真不赖，字段将近一百个,而且原表记录数有15000多条。所以可能真的把临时表空间吃完。于是对涉及到的基表做了一个空间占用情况统计：
SQL> set serveroutput on
SQL> exec show_space('ENROL_EXAMINEE','AUTO');
Total Blocks............................2432
Total Bytes.............................19922944
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................11
Last Used Ext BlockId...................301576
Last Used Block.........................128

计算一下基表占用19922944字节，大约是19M。还不包括其它的表的字段统计。先将temp数据文件自动扩展。先不管这个sql语句是否优化，做一个执行计划和统计分析，得到如下结果：

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| Id | Operation                     | Name                       | Rows | Bytes |TempSpc| Cost |
|   0 | SELECT STATEMENT              |                             | 9023K| 9130M|       | 3579K|
|   1 | SORT ORDER BY                |                             | 9023K| 9130M|    19G| 3579K|
|* 2 |   HASH JOIN                   |                             | 9023K| 9130M|       |   439 |
|   3 |    TABLE ACCESS FULL          | ENROL_EXAMINEE              | 15146 |   310K|       |   231 |
|* 4 |    TABLE ACCESS BY INDEX ROWID| ENROL_EXAMINEE              |   596 |   591K|       |     2 |
|   5 |     NESTED LOOPS              |                             | 4171 | 4236K|       |   170 |
|   6 |      TABLE ACCESS FULL        | BASE_SPECIALITY             |    84 | 2016 |       |     2 |
|* 7 |      INDEX RANGE SCAN         | EXAMINEE_DEFAULTSPEC_INDEX |   202 |       |       |     1 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SYS_ALIAS_1"."TASKID"="ENROL_EXAMINEE"."TASKID")
   4 - filter("SYS_ALIAS_1"."CONTROLDEPARTMENT" IS NOT NULL)
   7 - access("SYS_ALIAS_1"."DEFAULTRECRUITSPEC"="Y"."SPECSTANDCODE")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
       filter("SYS_ALIAS_1"."DEFAULTRECRUITSPEC" IS NOT NULL)

Note: cpu costing is off

由此可知，临时表空间扩展到19G。这当然会把临时表空间全部吃掉。这时应该从sql语句本身来找问题。
那个sql语句基表大约有15000，而且涉及将近100个字段，最后还有一个非常耗费资源的排序操作。去掉那个order by 查看记录的返回数。
发现返回的记录数为26950729。这对我们数据库的硬件配置来说是一个很大数量级的数据，而且字段有非常多，所以占用既定的temp表空间的之后还不停的扩展，如果没有设置自动扩展，必然导致无法分配temp段。所以那个问题的产生就是必然的啦。现在追究为什么基表只有15000多条，而查询结构却是26950729条，所以怀疑是sql语句中存在cartesian，于是开始从业务需求来分析这个语句，果然是将最后一个(SELECT taskid,reexam FROM ENROL_EXAMINEE) b 中的基表应该是enrol_task而不是enrol_examinee，因为会产生两个表同个taskid来连接，而每个taskid下有很多学生，这就导致产生了一个庞大的cartesian乘积。最终导致对26950729条记录的排序而使临时表空间用尽。（此时没有自动扩展）将b结果集中ENROL_EXAMINEE用ENROL_TASK来替换，即使还有一个排序，结果也会在很短的时间内返回。

最后需要说明的是：
1、sql语句完成之后，需要检查记录的准确性。
2、尽量不要在视图中进行order by ，这是一个非常耗费资源的操作。

写下拉，以存警示！

ORA-01652:unable to extend temp segment by num in 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=’’;

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

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

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

SQL>ALTER 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 207381 in tablespace TEMPSPACE

相应的英文如下：

Cause: Failed to allocate extent for temp segment in tablespace

Action:Use the ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the specified tablespace or create the object in another tablespace.

补充：

重建Temp表空间

startup --启动数据库 create temporary tablespace TEMP2 TEMPFILE '/home2/oracle/oradata/sysmon/temp02.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED; --创建中转临时表空间 alter database default temporary tablespace temp2; --改变缺省临时表空间为刚刚创建的新临时表空间temp2 drop tablespace temp including contents and datafiles;--删除原来临时表空间 create temporary tablespace TEMP TEMPFILE '/home2/oracle/oradata/sysmon/temp01.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED; --重新创建临时表空间 alter database default temporary tablespace temp; --重置缺省临时表空间为新建的temp表空间 drop tablespace temp2 including contents and datafiles;--删除中转用临时表空间