dbms_lob儲存過程導致臨時表空間100%
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%的情況。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/69996316/viewspace-2886937/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【儲存管理】建立臨時表空間組、建立臨時表空間組及使用
- Sql儲存過程分頁--臨時表儲存SQL儲存過程
- SQL SERVER 臨時表導致儲存過程重編譯(recompile)的一些探討SQLServer儲存過程編譯Compile
- 【實驗】重建臨時表空間解決臨時表空間過大問題
- MySql 儲存過程 臨時表 無法插入資料MySql儲存過程
- mysql臨時表空間不夠導致主從複製失敗MySql
- oracle 臨時表空間Oracle
- oracle臨時表空間Oracle
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法！Oracle
- sql server 儲存過程中使用變數表,臨時表的分析(續)SQLServer儲存過程變數
- 遷移表結構時儲存空間過大問題
- 批量處理時臨時增加回滾表空間臨時表空間檔案
- Cancel 刪除 正在使用的臨時表空間的操作 將導致異常
- 臨時表空間過大的解決方法
- 臨時表空間的建立、刪除，設定預設臨時表空間
- sqlserver 臨時表 遊標 儲存過程拼sql 日期函式等SQLServer儲存過程函式
- mysql臨時表，臨時表空間，ibtmp1表空間暴增原因初探MySql
- Oracle 臨時表空間概念Oracle
- oracle臨時表空間組Oracle
- oracle的臨時表空間Oracle
- Oracle Temp 臨時表空間Oracle
- 【臨時表空間組】臨時表空間組的建立、維護及應用
- 【臨時表空間】11g中使用 SHRINK方法縮小臨時表空間和臨時檔案
- oracle臨時表空間過大的原因&&處理Oracle
- ORACLE 臨時表空間使用率過高分析Oracle
- 【儲存管理】表空間概念
- 【臨時表空間】11g中使用 SHRINK方法縮小臨時表空間和臨時檔案（續）
- oracle臨時表空間相關Oracle
- MySQL InnoDB臨時表空間配置MySql
- Oracle TEMP臨時表空間概念Oracle
- Oracle 臨時表空間的概念Oracle
- 臨時表空間操作總結
- ORACLE臨時表空間總結Oracle
- oracle 重建臨時表空間 tempfileOracle
- 刪掉Oracle臨時表空間Oracle
- ORACLE臨時表空間的清理Oracle
- oracle 表空間，臨時表空間使用率查詢Oracle
- oracle goldengate 目標端表空間滿導致程式abended處理過程OracleGo