`

[转]解决ora-01652无法通过128(在temp表空间中)扩展temp段的过程

阅读更多
Tag:

http://www.oraclefans.cn/forum/showblog.jsp?rootid=4625

解决ora-01652无法通过128(在temp表空间中)扩展temp段的过程
昨天开发人员跟我说,执行一个sql语句后,大约花了10分钟,好不容易有一个结果,但是报了一个ora-01652错误,查阅了oracle的错误代码说明:意思是指temp表空间无法自动扩展temp段。这种问题一般有两种原因:一是临时表空间空间太小,二是不能自动扩展。

分析过程:

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

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

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

   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-01654处理表空间不足问题.pdf

    ORA-01654处理表空间不足问题 Oracle 数据库中,表空间不足是非常常见的...解决ORA-01654处理表空间不足问题需要通过查看表空间使用情况、查看表空间自动扩展功能、扩大原表空间大小或增加新的数据文件等方法来实现。

    oracle数据库操作常见错误以及解决方案

    其次,错误`ORA-01652: unable to extend temp segment by num in tablespace name`是由于临时表空间不足造成的。临时表空间主要用于存储排序和临时结果。当Oracle无法在当前临时表空间找到连续的空间来扩展临时段时...

    ORACLE修改表空间大小

    其中一种常见问题是ORA-01652错误,即“无法在指定表空间扩展临时段”。这通常发生在对临时表空间的操作上。当Oracle数据库执行涉及排序、连接等操作时,会使用临时表空间来存储临时数据。如果这些操作所需的空间...

    Oracle常见错误代码的分析与解决

    这个错误意味着回滚段在指定的表空间中无法扩展,通常是由于执行大规模的数据操作导致现有的回滚段不足以存储这些操作的回滚信息。回滚段是事务管理的关键组成部分,用于撤销未完成的事务。 **解决方式**: 1. ...

    orcale错误代码

    7. 其他错误如ORA-01630至ORA-01633涉及到表空间的temp段和数据段达到最大扩展限制,需要检查表空间的扩展策略,或者考虑增加新的数据文件。 解决这些Oracle错误通常需要对数据库的内部工作原理有深入理解,包括...

    Oracle数据库操作常见错误及解决方案

    本文将深入解析两个常见的Oracle错误及其解决方案:ORA-01650和ORA-01652,并提供相关的排查和解决策略。 **ORA-01650: unable to extend rollback segment NAME by NUM in tablespace NAME** 这个错误表明回滚段...

    Oracle释放临时表空间脚本

    在Oracle数据库管理中,临时表空间(Temporary Tablespace)的管理是一项非常重要的任务。合理的管理和优化临时表空间不仅可以提高系统的性能,还可以帮助解决空间不足等问题。本文将详细介绍如何通过SQL脚本进行...

    ORACLE常见错误代码的分析与解决

    当Oracle数据库在执行排序操作或其他需要临时空间的操作时,如果临时表空间中没有足够的空间来扩展临时段,则会抛出ORA-01652错误。 **原因分析:** 1. **临时表空间空间不足:** 如果临时表空间的总容量不足以...

    oracle常见错误解析

    5. ORA-01652: 无法在temp表空间扩展 当临时表空间不足以完成操作,如排序或连接,就会出现这个错误。增加临时表空间的大小,或者分配更多的临时段,可以解决这个问题。 6. ORA-01722: 无效数字 这个错误表示在尝试...

    oracle常用命令及错误类型(error message)

    10. ORA-01652:无法在临时表空间扩展 - 解决:增大临时表空间的大小,或调整SQL语句以减少排序需求。 学习Oracle命令和理解错误类型是掌握Oracle数据库管理的关键步骤。通过实践这些命令和解决常见错误,可以提高...

    oracle常见错误代码的分析与解决.doc

    其次,错误代码ORA-01652表示"unable to extend temp segment by num in tablespace name",意味着Oracle在尝试扩展临时段时遇到了表空间不足的问题。临时段主要用于存储排序和临时结果集,当无法分配连续空间时会...

    oracle创建表空间和用户

    在Oracle数据库系统中,表空间(Tablespace)是存储数据的主要单位,而用户(User)则是访问数据库并操作数据的实体。本节将详细介绍如何在Oracle中创建表空间和用户,以及相关的配置步骤。 首先,我们来理解表空间...

    streamsets数据采集常见问题汇总1

    问题描述:遇到`ORA-01652: unable to extend temp segment by 128 in tablespace TEMP`错误,这表明Oracle数据库无法在临时表空间中扩展段。 解决方法:这可能是由于数据排序导致的。建议使用单表消费方式,通过...

    oracle创建表空间实例代码

    根据提供的文件信息,本文将详细解析Oracle数据库中的表空间管理和用户管理相关知识点,包括如何创建表空间、设置表空间属性以及如何创建用户并授予相应权限等。 ### 一、Oracle表空间管理 #### 1. 创建表空间 表...

    ORACLE新建用户表空间及授权DBA

    在Oracle数据库管理中,创建用户、表空间以及进行权限分配是数据库管理员(DBA)的核心职责之一。本文将根据提供的文件信息,深入解析如何在Oracle环境中新建用户表空间,并授予DBA级别的权限,确保数据存储与访问的...

    linux oracle 建表,表空间以及删除命令

    在 Oracle 数据库中,可以通过 ALTER TABLESPACE 命令来改变表空间的状态。包括: * 使表空间脱机:ALTER TABLESPACE game OFFLINE; * 使表空间联机:ALTER TABLESPACE game ONLINE; * 使数据文件脱机:ALTER ...

    ORACLE常见错误代码的分析与解决(一)

    这个错误通常表示回滚段表空间不足,即在执行大型数据操作时,现有的回滚段不足以支持操作,导致表空间无法扩展。回滚段是Oracle用来存储事务回滚信息的关键组件,它确保了事务的原子性和一致性。解决这个问题的方法...

    001 创建表空间与用户和说明文档

    `LOGGING`表示该表空间将记录事务日志,`EXTENTMANAGEMENT LOCAL`和`SEGMENTSPACEMANAGEMENT AUTO`表示使用本地管理的段空间管理。 #### 临时表空间(Temporary Tablespaces) 临时表空间主要用于存储排序操作、...

    Oracle创建表空间.创建用户.创建表

    通过以上步骤,我们可以完成在Oracle数据库中创建表空间、用户以及表的过程。需要注意的是,每一步都需要仔细考虑相关的配置项,确保数据库的安全性和性能。此外,Oracle数据库还提供了丰富的管理和维护工具,可以...

Global site tag (gtag.js) - Google Analytics