ORA-01652 临时表空间不足

技术文章 2年前 (2020) 完美者
495 0

标签:import   dmi   via   esc   占用   uid   扩容   ever   storage   



   现象:应用反馈SQL无法执行成功,或者DB Alert告警报错 ORA-01652,临时表空间xx报错,不足。




   临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里需要用到SORT的时候,并且当PGA中sort_area_size大小不够时,将会把数据放入临时表空间里进行排序。像数据库中一些操作: CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等都可能会用到临时表空间。当操作完成后,系统会自动清理临时表空间中的临时对象,自动释放临时段。这里的释放只是标记为空闲、可以重用,其实实质占用的磁盘空间并没有真正释放。这也是临时表空间有时会不断增大的原因


    定位到了问题SQL如何处理,是扩容? 还是调优?



TROUBLESHOOTING GUIDE (TSG) : ORA-1652: unable to extend temp segment (Doc ID 1267351.1)    

First it is important to forget what is known about past behavior of the instance as the current tablespace size is insufficient to handle the demand by current sessions

There are three recommended methods of supplying sufficient storage space to meet the needs of current sessions by increasing the size of your temporary tablespace

1) Set one or more of the tempfiles|datafiles for the tablespace to AUTOEXTEND with MAXSIZE set ... so that you do not exhaust all available disk volume space
(discuss this with a System Administrator)

After a month or so of having this setting ... AUTOEXTEND can be disabled .. as it is likely that the system has found its true high watermark for temporary segment usage

(This is the most recommended method as it allows the database instance to find its own high watermark)

2) Monitor the temporary segment usage via queries like

SELECT sum(blocks)*<block size of the temporary tablespace>
FROM v$tempseg_usage
WHERE tablespace = ‘<name of the temporary tablespace>‘;

and resize one or more of the tempfiles|datafiles for the tablespace as the tablespace becomes close to exhausted

3) Add a tempfile|datafile to the temporary tablespace with the problem and monitor usage as described in #2

Another good idea is to monitor temporary tablespace usage over time to determine what queries are consuming the temporary space space

For example: How Can Temporary Segment Usage Be Monitored Over Time? (Doc ID 364417.1)

This note was written to monitor temporary tablespaces .. but may be able to be modified to also monitor permanent tablespaces

If after adjusting the ‘granularity‘ of the monitoring job (Doc ID 364417.1) to a small sampling period (1 min should be sufficient) it is found that there truly are never any points in time where temporary segment usage approaches the point where the tablespace has been exhausted then we recommend opening a service request via My Oracle Support so a possible bug can be investigated


查询历史某个时间节点,或者最近情况快速定位都可以,使用ASH OR AWR视图
SQL>  select  max(round(a.TEMP_SPACE_ALLOCATED/1024/1024/1024,2))G,to_char(a.SAMPLE_TIME,yyyy-mm-dd hh24:mi) as "date",a.inst_id,
a.SESSION_ID,a.SESSION_SERIAL#,b.username,a.event,a.sql_id,a.MACHINE from gv$active_session_history a,dba_users b where a.user_id=b.user_id and a.SAMPLE_TIME>sysdate-1 and a.TEMP_SPACE_ALLOCATED>2*1024*1024*1024 group by to_char(a.SAMPLE_TIME,yyyy-mm-dd hh24:mi),a.inst_id,a.SESSION_ID,a.SESSION_SERIAL#,b.username,a.event,a.sql_id,a.MACHINE order by 1,2,3,4,5,6 G date INST_ID SESSION_ID SESSION_SERIAL# USERNAME EVENT SQL_ID MACHINE ---------- ----------------------------- ----------------- ------------------------------ ------------- ---------------------------- 2.01 2019-10-08 10:30 1 418 9573 xx 727rff0t7anyw xx 2.16 2019-10-08 14:39 1 1058 6865 xx direct path write temp apc755z6ghzux xx


SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024/1024,MAXBYTES/1024/1024/1024,USER_BYTES/1024/1024/1024 from dba_temp_files;



SQL> select * from dba_tablespace_groups;





2.业务无法优化,我们观察发现存在例如 !笛卡尔积导致的占用TEMP问题,DBA优化SQL进行处理;
3.上述方法无法操作,从运维的角度只能扩容 !例如,数据泵导入时,自动创建索引等,需要占用较多的临时表空间 


ORA-01652 临时表空间不足

标签:import   dmi   via   esc   占用   uid   扩容   ever   storage   


版权声明:完美者 发表于 2020-11-07 16:49:46。
转载请注明:ORA-01652 临时表空间不足 | 完美导航