数据库版本11204RAC
执行占用temp表空间sql,发现执行sql后temp表空间不释放
SQL> select TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024 ,FREE_SPACE/1024/1024 from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE/1024/1024 FREE_SPACE/1024/1024
------------------------------ ------------------------- --------------------
TEMP 50 49
SQL> select wm_concat(id) from test;WM_CONCAT(ID)
--------------------------------------------------------------------------------
20,46,28,15,29,3,25,41,54,40,26,17,13,9,43,51,38,7,56,19,14,6,44,21,45,35,5,23,4
SQL> select TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024 ,FREE_SPACE/1024/1024 from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE/1024/1024 FREE_SPACE/1024/1024
------------------------------ ------------------------- --------------------
TEMP 50 4
SQL> select distinct(sid) from v$mystat;SID
----------
69
查看执行sql的,发现是占用temp表空间数据类型为LOB_DATA
SQL> select b.inst_id,a.sid,b.ktssosno serial#,b.ktssosqlid sql_id,b.ktssoblks*8/1024 mb,c.sql_text,d.tablespace,d.segtype from v$session a,x$ktsso b,v$sql c,v$sort_usage d where b.ktssoses=a.saddr and b.ktssosno=a.serial# and b.ktssosqlid=c.sql_id and d.session_addr=b.ktssoses and b.ktssosno=d.session_num;
INST_ID SID SERIAL# SQL_ID MB SQL_TEXT TABLESPACE SEGTYPE
---------- ---------- ---------- ------------- ---------- ------------------------------ ------------------------------- ---------
1 69 7 63mbdmx49z7a5 45 select wm_concat(id) from test TEMP LOB_DATA
wm_concat() 函数在10g返回varchar 类型,在11g时返回clob类型,建议用listagg()替换wm_concat()。
解决方案
1、强制中断会话
SQL> select TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024 ,FREE_SPACE/1024/1024 from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE/1024/1024 FREE_SPACE/1024/1024
------------------------------ ------------------------- --------------------
TEMP 50 4
SQL> select b.inst_id,a.sid,b.ktssosno serial#,b.ktssosqlid sql_id,b.ktssoblks*8/1024 mb,c.sql_text,d.tablespace,d.segtype from v$session a,x$ktsso b,v$sql c,v$sort_usage d where b.ktssoses=a.saddr and b.ktssosno=a.serial# and b.ktssosqlid=c.sql_id and d.session_addr=b.ktssoses and b.ktssosno=d.session_num;
INST_ID SID SERIAL# SQL_ID MB SQL_TEXT TABLESPACE SEGTYPE
---------- ---------- ---------- ------------- ---------- ------------------------------ ------------------------------- ---------
1 69 7 63mbdmx49z7a5 45 select wm_concat(id) from test TEMP LOB_DATA
SQL> alter system disconnect session '69,7' immediate;
System altered.
SQL> select TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024 ,FREE_SPACE/1024/1024 from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE/1024/1024 FREE_SPACE/1024/1024
------------------------------ ------------------------- --------------------
TEMP 50 49
2、在执行语句之前设置session级别60025事件
SQL> select TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024 ,FREE_SPACE/1024/1024 from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE/1024/1024 FREE_SPACE/1024/1024
------------------------------ ------------------------- --------------------
TEMP 50 49
SQL> alter session set events '60025 trace name context forever';
Session altered.
SQL> select wm_concat(id) from test;
WM_CONCAT(ID)
--------------------------------------------------------------------------------
20,46,28,15,29,3,25,41,54,40,26,17,13,9,43,51,38,7,56,19,14,6,44,21,45,35,5,23,4
SQL> select TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024 ,FREE_SPACE/1024/1024 from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE/1024/1024 FREE_SPACE/1024/1024
------------------------------ ------------------------- --------------------
TEMP 50 49
3 、使用DBMS_LOB.FREETEMPORARY
未做测试。
参考文档
How to Release the Temp LOB Space and Avoid Hitting ORA-1652 (文档 ID 802897.1)