1 数据库告警如下：
Thu Mar 24 10:09:34 2022
Archived Log entry 364525 added for thread 3 sequence 38346 ID 0xa132907e dest 1:
Thu Mar 24 10:13:22 2022
ORA-1652: unable to extend temp segment by 128 in tablespace APPTMP
Thu Mar 24 10:13:22 2022
ORA-1652: unable to extend temp segment by 128 in tablespace APPTMP
......
2 查看临时表空间使用情况，确实不够用了
TABLESPACE_NAME Total(MB) Used(MB) Free(MB) Pct. Free(%)
------------------------------ ---------- ---------- ---------- ------------
APPTMP 215040 202051 12989 6.04
TEMP 155647 2 155645 100
APP2TMP 28672 0 28672 100
3 通过如下查看，SELECT 1 FROM DUAL占用了76G的临时表空间，从侧面能够说明应用会话执行完相关操作没有释放资源。
USERNAME TABLESPACE SQL_ID SQL_TEXT MB_USED
--------------- --------------- ------------- ---------------------------------------------------------------------- ----------
TEST_SX APPTMP 2h91m65182n2g select count(1) from app_schetest where app_no = :1 and chg_desc 3543
= '01' and phase_code is null
.....
TEST_SX APPTMP bunvx480ynf57 SELECT 1 FROM DUAL 76944
4 查看哪些会话占用临时表空间多，经详查，发现有60多会话使用临时表空间在3G左右，其余都很小。这能够说明有多个会话同时使用临时表空间，导致临时表空间达到100%。一种情况为并发太高引起的，第二种情况为应用不释放资源，导致的。
SQL> set linesize 300
SQL> set pagesize 999
SQL> set long 9999
SQL> col username format a10
SQL> col tablespace format a10
SQL> col sql_text format a60
SQL> col kill_session for a50
SQL> select s.username,'alter system kill session ''' || s.sid || ','||s.serial# ||''' immediate;' "kill_SESSION",
o.blocks*t.block_size/1024/1024 MB_USED,o.tablespace,h.SQL_ID,h.sql_text from v$sort_usage o,v$session s,
v$sqlarea h,dba_tablespaces t
where o.session_addr=s.saddr and o.sqladdr=h.address(+) and o.tablespace = t.tablespace_name
order by MB_USED;
USERNAME kill_SESSION MB_USED TABLESPACE SQL_ID SQL_TEXT
---------- -------------------------------------------------- ---------- ---------- ------------- ------------------------------------------------------------
...................
TEST_SX alter system kill session '995,54343' immediate; 2962 APPTMP bunvx480ynf57 SELECT 1 FROM DUAL
TEST_SX alter system kill session '4202,63805' immediate; 2963 APPTMP 9pw4fx2gs2u68 select *****
TEST_SX alter system kill session '1956,60741' immediate; 2963 APPTMP a4r6c5x5af64y insert into *****
......
TEST_SX alter system kill session '38,47453' immediate; 2964 APPTMP bunvx480ynf57 SELECT 1 FROM DUAL
5 查看SELECT 1 FROM DUAL 占用临时表空间的会话,发现许多进程都占用了3G大小的临时表空间
USERNAME kill_SESSION MB_USED TABLESPACE SQL_ID SQL_TEXT
---------- ------------ -------- -------------------------------------------------- ---------- ---------- ------------- --------------------
.......
TEST_SX alter system kill session '1220,60857' immediate; 3019 APPTMP bunvx480ynf57 SELECT 1 FROM DUAL
TEST_SX alter system kill session '3620,43733' immediate; 3050 APPTMP bunvx480ynf57 SELECT 1 FROM DUAL
TEST_SX alter system kill session '3617,11195' immediate; 3099 APPTMP bunvx480ynf57 SELECT 1 FROM DUAL
TEST_SX alter system kill session '1801,59937' immediate; 3099 APPTMP bunvx480ynf57 SELECT 1 FROM DUAL
。。。。。。。。。。。。。。。
TEST_SX alter system kill session '4451,33231' immediate; 3255 APPTMP bunvx480ynf57 SELECT 1 FROM DUAL
TEST_SX alter system kill session '3500,44035' immediate; 3279 APPTMP bunvx480ynf57 SELECT 1 FROM DUAL
TEST_SX alter system kill session '3689,33797' immediate; 3284 APPTMP bunvx480ynf57 SELECT 1 FROM DUAL
TEST_SX alter system kill session '1669,23003' immediate; 3307 APPTMP bunvx480ynf57 SELECT 1 FROM DUAL
6 根据历史会话信息，查看会话执行的SQL_ID
select sh.instance_number,to_char(sh.sample_time,'yyyy-mm-dd hh24:mi:ss') as TIME_SA,sh.session_id,
sh.session_serial#,sh.sql_id,sh.wait_class,
sh.blocking_session,sh.blocking_session_serial# from dba_hist_active_sess_history sh
where sh.session_id='1669' and sh.session_serial#='23003' order by sh.sample_time ;
INSTANCE_NUMBER TIME_SA SESSION_ID SESSION_SERIAL# SQL_ID WAIT_CLASS BLOCKING_SESSION BLOCKING_SESSION_SERIAL#
--------------- ------------------------------ ---------- --------------- -------------------- -------------------- ---------------- ------------------------
3 2022-03-24 16:20:59 1669 23003 czwjc8qtnrh8r
3 2022-03-24 16:23:30 1669 23003 aagj08z6fydgx
3 2022-03-24 16:26:41 1669 23003 06x6rxk3ck6y6
3 2022-03-24 16:51:36 1669 23003
3 2022-03-24 16:52:16 1669 23003 5z7qj3dsx0uf1
3 2022-03-24 16:53:16 1669 23003 8thusy330zzt4
3 2022-03-24 17:02:48 1669 23003 06x6rxk3ck6y6
3 2022-03-24 17:03:18 1669 23003 6xt22jtx75xp9
3 2022-03-24 17:03:28 1669 23003 6xt22jtx75xp9
3 2022-03-24 17:03:38 1669 23003 6xt22jtx75xp9
3 2022-03-24 17:03:48 1669 23003 6xt22jtx75xp9
3 2022-03-24 17:03:58 1669 23003
3 2022-03-24 17:05:19 1669 23003 9j332xy4h4zuu
3 2022-03-24 17:30:24 1669 23003 dk473q5w5kg0t
3 2022-03-24 17:30:34 1669 23003 dk473q5w5kg0t
3 2022-03-24 17:30:44 1669 23003 abdmu7fk203w3
16 rows selected.
7 使用如下SQL，可以查询出那些SQL开始耗费临时表空间，首先发现的为6xt22jtx75xp9，
此SQL同第6步执行的SQLID重复，故查看此SQL的TEXT文本
select snap_id,instance_number,SAMPLE_TIME,session_id,session_serial#,sql_id,program,module,
temp_space_allocated/1024/1024 "MB" from dba_hist_active_sess_history
where temp_space_allocated/1024/1024 >10 and
sample_time between to_timestamp ('2022-03-24 10:00:00', 'yyyy-mm-dd hh24:mi:ss')
and to_timestamp ('2022-03-24 10:30:00', 'yyyy-mm-dd hh24:mi:ss')
order by instance_number,SAMPLE_TIME
8 查看6xt22jtx75xp9的TEXT文本,此文本有使用dbms_lob存储过程，此存储过程返回的值为LOB，且会话不结束不释放临时表空间资源。
select table_test1.app_ID as "app_ID",
......
(select dbms_lob.substr(wm_concat(table_test1_load.load_prop),4000, 1)
from table_test1, table_test1_load
where table_test1.app_ID = table_test1_load.app_ID) as "LOAD_PROP",
................
from table_test1
where table_test1.RESAREA_ID = :1
9 以下为Oracle官方文档针对dbms_lob针对Temporary LOBs说明，且说明会话不结束，不释放临时表空间
Temporary LOBs
The database supports the definition, creation, deletion, access, and update of temporary LOBs.
Your temporary tablespace stores the temporary LOB data.
Temporary LOBs are not permanently stored in the database.
Their purpose is mainly to perform transformations on LOB data.
For temporary LOBs, you must use the OCI, PL/SQL,
or another programmatic interface to create or manipulate them.
Temporary LOBs can be either BLOBs, CLOBs, or NCLOBs.
A temporary LOB is empty when it is created. By default, all temporary LOBs are deleted at the end of the session
in which they were created.
If a process dies unexpectedly or if the database crashes, then temporary LOBs are deleted,
and the space for temporary LOBs is freed.--此段说明只有会话结束或终止，才会释放临时表空间
There is also an interface to let you group temporary LOBs together into a logical bucket.
The duration represents this logical store for temporary LOBs.
Each temporary LOB can have separate storage characteristics, such as CACHE/ NOCACHE.
There is a default store for every session into which temporary LOBs are placed
if you don't specify a specific duration. Additionally, you are able to perform a free operation on durations,
which causes all contents in a duration to be freed.
There is no support for consistent read (CR), undo, backup, parallel processing,
or transaction management for temporary LOBs. Because CR
and rollbacks are not supported for temporary LOBs, you must free the temporary LOB and start over again
if you encounter an error.
Because CR, undo, and versions are not generated for temporary LOBs, there is potentially a performance impact
if you assign multiple locators to the same temporary LOB. Semantically,
each locator should have its own copy of the temporary LOB.
A copy of a temporary LOB is created if the user modifies the temporary LOB
while another locator is also pointing to it.
The locator on which a modification was performed now points to a new copy of the temporary LOB.
Other locators no longer see the same data as the locator through
which the modification was made. A deep copy was not incurred by permanent LOBs in these types of situations,
because CR snapshots and version pages enable users to see
their own versions of the LOB cheaply.
10 测试DBMS_LOB是否在会话结束前不释放资源，经验证，使用DBMS_LOB存储过程，在会话结束前不释放临时表空间的资源。
10.1 查看当前会话的相关信息
SQL> select s.sid,s.SERIAL#,p.pid,p.spid from v$session s,v$process p
where s.paddr = p.addr and s.sid in (select sid from v$mystat);
SID SERIAL# PID SPID
---------- ---------- ---------- ------------------------
2913 64533 91 293671
10.2 查看2913会话使用临时表空间的信息
SQL> select s.username,'alter system kill session ''' || s.sid || ','||s.serial# ||''' immediate;
' "kill_SESSION",o.blocks*t.block_size/1024/1024 MB_USED,o.tablespace,h.SQL_ID,h.sql_text from
v$sort_usage o,v$session s,v$sqlarea h,dba_tablespaces t
where s.sid=2913 and s.serial#=64533 and o.session_addr=s.saddr
and o.sqladdr=h.address(+) and o.tablespace = t.tablespace_name order by MB_USED;
no rows selected
10.3 在2913会话中执行如下SQL，验证是否使用临时表空间
select table_test1.app_ID as "app_ID",
......
(select dbms_lob.substr(wm_concat(table_test1_load.load_prop),4000, 1)
from table_test1, table_test1_load
where table_test1.app_ID = table_test1_load.app_ID) as "LOAD_PROP",
................
from table_test1
where table_test1.app_ID = 402 and rownum < 10;
10.4 再次进行查看，验证会话使用临时表空间，可以发现使用TEMP临时表空间从0变为4130M，
即使会话执行完成，临时表空间也没有进行释放。
SQL> /
USERNAME kill_SESSION MB_USED TABLESPACE SQL_ID SQL_TEXT
---------- -------------------------------------------------- ---------- ---------- ------------- ------------------------------------------------------------
SYS alter system kill session '2913,64533' immediate; 188 TEMP cgba2mcagzhxm select s.sid,s.SERIAL#,p.pid,p.spid from v$session s,v$proc
ess p where s.paddr = p.addr and s.sid in (select sid from v
$mystat
SQL> /
USERNAME kill_SESSION MB_USED TABLESPACE SQL_ID SQL_TEXT
---------- -------------------------------------------------- ---------- ---------- ------------- ------------------------------------------------------------
SYS alter system kill session '2913,64533' immediate; 265 TEMP cgba2mcagzhxm select s.sid,s.SERIAL#,p.pid,p.spid from v$session s,v$proc
ess p where s.paddr = p.addr and s.sid in (select sid from v
$mystat)
SYS alter system kill session '2913,64533' immediate; 2977 TEMP cgba2mcagzhxm select s.sid,s.SERIAL#,p.pid,p.spid from v$session s,v$proc
ess p where s.paddr = p.addr and s.sid in (select sid from v
$mystat)
SQL> /
USERNAME kill_SESSION MB_USED TABLESPACE SQL_ID SQL_TEXT
---------- -------------------------------------------------- ---------- ---------- ------------- ------------------------------------------------------------
SYS alter system kill session '2913,64533' immediate; 350 TEMP g5mfkdcpwbs3d select table_test1.app_ID as "app_ID",
......
(select dbms_lob.substr(wm_concat(table_test1_load.load_prop),4000, 1)
from table_test1, table_test1_load
where table_test1.app_ID = table_test1_load.app_ID) as "LOAD_PROP",
................
from table_test1
where table_test1.app_ID = 402 and rownum < 10;
SYS alter system kill session '2913,64533' immediate; 4130 TEMP g5mfkdcpwbs3d select table_test1.app_ID as "app_ID",
......
(select dbms_lob.substr(wm_concat(table_test1_load.load_prop),4000, 1)
from table_test1, table_test1_load
where table_test1.app_ID = table_test1_load.app_ID) as "LOAD_PROP",
................
from table_test1
where table_test1.app_ID = 402 and rownum < 10;
10.5 查看临时表空间使用的情况，从2M变为4484M
TABLESPACE_NAME Total(MB) Used(MB) Free(MB) Pct. Free(%)
------------------------------ ---------- ---------- ---------- ------------
APPTMP 215040 4129 210911 98.08
TEMP 155647 2 155645 100
APP2TMP 28672 0 28672 100
SQL> /
TABLESPACE_NAME Total(MB) Used(MB) Free(MB) Pct. Free(%)
------------------------------ ---------- ---------- ---------- ------------
APPTMP 215040 7193 207847 96.66
TEMP 155647 2108 153539 98.65
APP2TMP 28672 0 28672 100
SQL> /
TABLESPACE_NAME Total(MB) Used(MB) Free(MB) Pct. Free(%)
------------------------------ ---------- ---------- ---------- ------------
APPTMP 215040 4135 210905 98.08
TEMP 155647 4484 151163 97.12
APP2TMP 28672 0 28672 100
10.6 等待10分钟，临时表空间也没有释放，并查看临时表空间使用的类型，
发现TEMP表空间的LOB_DATA类型占用了4130M，同查询出来的结果一致。
INST_ID TABLESPACE SEGTYPE COUNT(*) USED_MB
---------- ---------- --------- ---------- ----------
3 TEMP LOB_DATA 1 4130
3 APPTMP LOB_DATA 58 3810
3 TEMP LOB_INDEX 1 350
3 APPTMP LOB_INDEX 1 324
2 APPTMP DATA 1 9
2 TEMP DATA 3 3
2 TEMP INDEX 3 3
3 TEMP DATA 2 2
1 TEMP INDEX 2 2
1 TEMP DATA 2 2
3 TEMP INDEX 2 2
4 TEMP LOB_DATA 2 2
4 TEMP DATA 1 1
4 TEMP INDEX 1 1
14 rows selected.
10.7 关闭会话，临时表空间得以释放，如下：
SQL> /
TABLESPACE_NAME Total(MB) Used(MB) Free(MB) Pct. Free(%)
------------------------------ ---------- ---------- ---------- ------------
APPTMP 215040 4139 210901 98.08
TEMP 155647 4 155643 100
APP2TMP 28672 0 28672 100
总结：根据如上信息，由于应用模块使用长连接，会话不结束，且使用DBMS_LOB存储过程,临时表空间不释放，
最终引起临时表空间达到100%的情况。