Know more about Temporary Tablespaces ORA-1652
众所周知, oracle临时表空间是用于存储数据库的排序数据和全局临时表数据，tempfile不同于datafile因为不存在控制文件中，在恢复数据库后需要重建tempfile,也可以在standby环境中配置不同的temp表空间。用监控数据表空间间的脚本也许看到临时表空间使用率100%，但并且有时并非真实，DBA_FREE_SPACE 不会记录临时表空间的可用空间，使用v$sort_segments,V$TEMP_SPACE_HEADER和v$tempseg_usage监控临时表空间的使用，如果临时表空间不足会提示ORA-1652错误。对于临时表空间组特性是没有用的，为不同的用户分配不同的temp tablespace并且以用户分配到不同的实例可以减少temp空间争用。
临时表空间是被分成不同的extent,在11g r2中每个extent为1MB，extent maps是cache在本地的SGA中，并在再重启时不会保存extent map所以在实例重启后可以完全释放临时表空间的扩展， 临时表空间(cache extent map)是按需扩展的，用完后会标记为free但不会回收(如果不重启)，可以使用v$tempseg_usege查看当前实际使用。本实例cache的extent即使为free,对于其他实例也不可以立即使用，另一个实例需要申请新的uncache的extent, free extent只重用于本实例请求。当一个实例需要更多的extent时可以从另一个实例偷窃，另一个实例会uncache extent map，可以从gv$temp_extent_pool中观察,在11g中为一次请求100 extents,当本实例窃取够了足够的extent就可以继续使用temp extent，临时段的清理或合并是有每5分钟smon进程完成的。
SQL> select * from gv$temp_extent_pool;
INST_ID TABLESPACE_NAME FILE_ID EXTENTS_CACHED EXTENTS_USED BLOCKS_CACHED BLOCKS_USED BYTES_CACHED BYTES_USED RELATIVE_FNO
---------- ----------------- ---------- -------------- ------------ ------------- ----------- ------------ ---------- ------------
1 TEMP 1 11916 8 1525248 1024 1.2495E+10 8388608 1
1 TEMP 3 2884 1 369152 128 3024093184 1048576 3
1 TEMP 4 5990 3 766720 384 6280970240 3145728 4
1 TEMP 5 9422 1 1206016 128 9879683072 1048576 5
1 TEMP 6 10659 3 1364352 384 1.1177E+10 3145728 6
2 TEMP 1 18347 0 2348416 0 1.9238E+10 0 1
2 TEMP 3 12474 2 1596672 256 1.3080E+10 2097152 3
2 TEMP 4 9369 0 1199232 0 9824108544 0 4
2 TEMP 5 5937 3 759936 384 6225395712 3145728 5
近期有一套数据库出现了ORA-1652,extent map都已cache，显示使用率为100%，但当前真实使用的并不多，重启数据库不现实，我想通过drop tempfile再填加的方式释放已扩展的temp空间，结果发现了一个有意思的事情。
1, Drop Tempfile
SQL> @df TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used ------------------------------ ---------- ---------- ---------- ------ --- ---------------------- ... SYSAUX 24318 17012 7306 70% NO |############## | SYSTEM 20478 2674 17804 14% NO |### | TEMP 107517 107474 43 100% YES |####################| UNDOTBS1 61438 35678 25760 59% NO |############ | UNDOTBS2 61438 13743 47695 23% NO |##### | USERS 330226 283772 46454 86% NO |################## | SQL> @ls temp TABLESPACE_NAME FILE_ID FILE_NAME EXT MB MAXSZ ------------------------------ ---------- ---------------------------------------- --- ---------- ---------- TEMP 3 /dev/yyc_oravg02/ryyc_lv15_128 NO 15359 TEMP 1 /dev/yyc_oravg02/ryyc_temp YES 30719 32767.98 TEMP 2 /dev/yyc_oravg03/ryyc_lv15_240 TEMP 4 /dev/yyc_oravg04/ryyc_lv15_281 NO 15359.99 TEMP 5 /dev/yyc_oravg04/ryyc_lv15_282 NO 15359.99 TEMP 6 /dev/yyc_oravg04/ryyc_lv15_283 NO 15359.99 TEMP 7 /dev/yyc_oravg04/ryyc_lv15_284 NO 15359.99 SQL> alter tablespace temp drop tempfile '/dev/yyc_oravg04/ryyc_lv15_284'; Tablespace altered. SQL> alter tablespace temp drop tempfile '/dev/yyc_oravg04/ryyc_lv15_283'; Tablespace altered. SQL> alter tablespace temp add tempfile '/dev/yyc_oravg04/ryyc_lv15_284'; alter tablespace temp add tempfile '/dev/yyc_oravg04/ryyc_lv15_284' * ERROR at line 1: ORA-01537: cannot add file '/dev/yyc_oravg04/ryyc_lv15_284' - file already part of database SQL> @ls temp TABLESPACE_NAME FILE_ID FILE_NAME EXT MB MAXSZ ------------------------------ ---------- -------------------------------------------------------------------------------- --- ---------- ---------- TEMP 3 /dev/yyc_oravg02/ryyc_lv15_128 NO 15359 TEMP 1 /dev/yyc_oravg02/ryyc_temp YES 30719 32767.98 TEMP 2 /dev/yyc_oravg03/ryyc_lv15_240 TEMP 4 /dev/yyc_oravg04/ryyc_lv15_281 NO 15359.99 TEMP 5 /dev/yyc_oravg04/ryyc_lv15_282 NO 15359.99 TEMP 6 /dev/yyc_oravg04/ryyc_lv15_283 TEMP 7 /dev/yyc_oravg04/ryyc_lv15_284 SQL> @df TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used ------------------------------ ---------- ---------- ---------- ------ --- ---------------------- ... SYSAUX 24318 17014 7304 70% NO |############## | SYSTEM 20478 2674 17804 14% NO |### | TEMP 76797 76754 43 100% YES |####################| UNDOTBS1 61438 35678 25760 59% NO |############ | UNDOTBS2 61438 13726 47712 23% NO |##### | USERS 330226 283772 46454 86% NO |################## |
NOTE:
使用alter tablespace无法drop tempfile(从数据库释放tempfile),但是可用空间被收回。 正确的方法是使用alter database方式如下：
SQL> alter database tempfile '/dev/yyc_oravg03/ryyc_lv15_240' drop including datafiles;
Database altered.
SQL> @ls temp
TABLESPACE_NAME FILE_ID FILE_NAME EXT MB MAXSZ
------------------------------ ---------- -------------------------------------------------------------------------------- --- ---------- ----------
TEMP 3 /dev/yyc_oravg02/ryyc_lv15_128 NO 15359
TEMP 1 /dev/yyc_oravg02/ryyc_temp YES 30719 32767.98
TEMP 4 /dev/yyc_oravg04/ryyc_lv15_281 NO 15359.99
TEMP 5 /dev/yyc_oravg04/ryyc_lv15_282 NO 15359.99
TEMP 6 /dev/yyc_oravg04/ryyc_lv15_283
TEMP 7 /dev/yyc_oravg04/ryyc_lv15_284
Note:
drop tempfile或drop temporary tablespace前提是当前无会话使用该文件和表空间，如果删除了所有的tempfile,在使用时会提示ora-25153错误，如果有会话使用该tempfile将报ORA-25152错误。
SQL> alter database tempfile '/dev/yyc_oravg04/ryyc_lv15_284' drop including datafiles; alter database tempfile '/dev/yyc_oravg04/ryyc_lv15_284' drop including datafiles * ERROR at line 1: ORA-25152: TEMPFILE cannot be dropped at this time SQL> select u.inst_id,s.sid,s.status,tablespace,u.sql_id,segfile#,segfile#-p.value rfile from gv$tempseg_usage u,v$parameter p,gv$session s where u.session_addr = s.saddr AND u.inst_id = s.inst_id and p.NAME='db_files'; INST_ID SID STATUS TABLESPACE SQL_ID SEGFILE# RFILE ---------- ---------- -------- ------------------------------- --------------- ---------- ---------- 1 17813 INACTIVE TEMP cbpmfx12jzj4r 2025 1 1 17530 INACTIVE TEMP 64tcqftuuwaan 2028 4 1 17285 INACTIVE TEMP cbpmfx12jzj4r 2025 1 ... 2 4814 INACTIVE TEMP cbpmfx12jzj4r 2025 1 2 4806 INACTIVE TEMP 6h214g82rxb7x 2027 3 1 4319 INACTIVE TEMP 64tcqftuuwaan 2030 6 1 1776 INACTIVE TEMP a6kb0pf8x0hqc 2029 5 2 51 INACTIVE TEMP cbpmfx12jzj4r 2025 1 1 285 INACTIVE TEMP 6h214g82rxb7x 2031 7 1 4 INACTIVE TEMP a6kb0pf8x0hqc 2028 4 SQL> select inst_id,tablespace,segtype,sum(blocks),count(*),sum(blocks)/count(*) from gv$tempseg_usage group by inst_id,tablespace,segtype; INST_ID TABLESPACE SEGTYPE SUM(BLOCKS) COUNT(*) SUM(BLOCKS)/COUNT(*) ---------- ------------------------------- --------- ----------- ---------- -------------------- 1 TEMP LOB_DATA 2176 17 128 2 TEMP LOB_DATA 1536 12 128
Note:
上面持用tempfile rfile #7的会话是connect pool的长连接，在当前的数据库版本11.2.0.3(其实在9i-12c的版本都存在)，持有的temp直到该session退出才会释放。如删除本案例中的tempfile rfile 7#,Rfile# 7的tempfile正在被实例1的sid=285的会话使用，kill 掉实例1的285会话即可删除7# tempfile。
并且当前2个实例上一共29个会话，占用的都是temp类型都是temp lob data.每个session占用1M(128*8k). 注意该视图中的SQL_ID并不是产生temp seg的SQL，而是该会话最后一次执行的SQL, 在12C版本中v$tempseg_usage新增加列”SQL_ID_TEMPSEG”可以确认产生temp segment的SQLID.如下：
— 12.2
SQL> select username,session_num,sql_id,tablespace,CONTENTS,SEGTYPE,SEGRFNO#,BLOCKS,SQL_ID_TEMPSEG from v$tempseg_usage; USERNAME SESSION_NUM SQL_ID TABLESPACE CONTENTS SEGTYPE SEGRFNO# BLOCKS SQL_ID_TEMPSE -------------------- ----------- ------------- ------------------------------ --------- --------- ---------- ---------- ------------- 18783 g44b5rqw7xwq7 TEMP TEMPORARY LOB_DATA 1 128 0w26sk6t6gq98 SQL> select sql_text from v$sqlarea where sql_id='0w26sk6t6gq98'; SQL_TEXT -------------------------- SELECT XMLTYPE(DBMS_REPORT.GET_REPORT_WITH_SUMMARY(:B1 )) FROM DUAL
Note: SQL_ID_TEMPSEG 是产生temp的SQL，XML 也是LOB一种形式。
According to Bug 5723140,since 9i onwards we have been using only one temp segment for temp lobs in a session and all these temp lobs share the same temp segment. When there is a request to cleanup those temp lobs we do free space from temp tablespace and the same session can reuse it, but the temp segment is not released which means it is not available for use to other sessions. The actual cleanup happens only on session exit thru the callback that was registered for this purpose. Hence multiple sessions using temp lobs will have these temp segments active though unused, thus leading to temp segment growth.
正如note中描述的从9i以后，session使用一个临时段存在所有temp lobs,但是用完后free空间，同一会话可以reuse, 临时段只有到exit时才会释放，否则一直占用。如果session 使用了connect pool的长连接，并且会话数增长，temp segment会逐渐增长，不会让其它会话使用。 从10.2.0.4提供了一个event 60025,当系统级或会话级启用了该event,会在session中的temp lob不活动时(cache temp和nocache temp lobs都为0时)，即使不退出会话也会自己释放空间供其它会话使用。如果启用了60025 event像下面这引起temp lob 会被释放掉:
SQL> select * from gv$temporary_lobs;
INST_ID SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
---------- ---------- ---------- ------------ -------------
2 4806 0 0 0
2 5159 0 0 0
2 6843 0 0 0
2 7975 0 0 0
2 8192 0 0 0
2 10728 0 0 0
1 4 0 0 0
1 1776 0 0 0
1 3460 0 0 0
1 4319 0 0 0
1 4799 0 0 0
1 4887 0 0 0
1 8220 0 0 0
1 8544 0 0 0
1 10478 0 0 0
1 11031 0 0 0
1 11110 0 0 0
1 11369 0 0 0
1 12983 0 0 0
1 14120 0 0 0
1 17285 0 0 0
1 17530 0 0 0
1 17813 0 0 0
1 17872 0 0 0
60025 event不可以动态在系统级修改生效，在spfile级修改重启生效；session级启用60025后，也不会立即释放之前的temp lobs.下面做个测试:
[oracle@weejar1:/home/oracle] ora
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 17 14:27:24 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
USERNAME INST_NAME HOST_NAME SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR
-------------------- -------------------- ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
SYS anbob1 weejar1 17872 50597 11.2.0.3.0 20171114 19239 3327 19235 C0000016152D86D8 C000001652FD99F0
-ne
SQL> declare
2 b blob;
3 begin
4 dbms_lob.CREATETEMPORARY(b,true);
5 dbms_lob.freeTEMPORARY(b);
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select * from gv$temporary_lobs;
INST_ID SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
---------- ---------- ---------- ------------ -------------
...
1 14120 0 0 0
1 17285 0 0 0
1 17530 0 0 0
1 17813 0 0 0
1 17872 0 0 0
24 rows selected.
SQL> select u.inst_id,s.sid,s.status,tablespace,u.sql_id,segfile#,u.blocks,segfile#-p.value rfile from gv$tempseg_usage u,v$parameter p,gv$session s where u.session_addr = s.saddr AND u.inst_id = s.inst_id and p.NAME='db_files';
INST_ID SID STATUS TABLESPACE SQL_ID SEGFILE# BLOCKS RFILE
---------- ---------- -------- ------------------------------- --------------- ---------- ---------- ----------
1 17872 INACTIVE TEMP1 g0wf0fkkqwaya 2033 128 9
1 17813 INACTIVE TEMP cbpmfx12jzj4r 2025 128 1
...
SQL> alter session set events '60025 trace name context forever';
Session altered.
SQL> select u.inst_id,s.sid,s.status,tablespace,u.sql_id,segfile#,u.blocks,segfile#-p.value rfile from gv$tempseg_usage u,v$parameter p,gv$session s where u.session_addr = s.saddr AND u.inst_id = s.inst_id and p.NAME='db_files';
INST_ID SID STATUS TABLESPACE SQL_ID SEGFILE# BLOCKS RFILE
---------- ---------- -------- ------------------------------- --------------- ---------- ---------- ----------
1 17872 INACTIVE TEMP1 g0wf0fkkqwaya 2033 128 9
1 17813 INACTIVE TEMP cbpmfx12jzj4r 2025 128 1
...
SQL> declare
2 b blob;
3 begin
4 dbms_lob.CREATETEMPORARY(b,true);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select * from gv$temporary_lobs;
-- sid 17872 released
Note:
因为temp seg同会话可以reuse, 再配置了60025 event后，再次执行lob操作后， temp lob自动清理。
系统级修改
alter system set event='60025 trace name context forever' scope=spfile;
对于已存在的会话可以使用oradebug 启用指定sid
oradebug setorapid <oracle pid> oradebug event 60025 trace name context forever -- wait the session next call lob to clean temp lob by itself
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。