We are currently experiencing a used space growth issue with PSAPTEMP tablespace. We had this issue about 2 months ago and it reached 100% used and it was only resolved by bouncing the DB.
I opened a SAP message and it was determined
"As per the attached document the most objects are lob segments
please refer to note 500340. "
I have revewied all the SAP notes that seem to be relavent to this but can't find an actual resolution to the issue.
When I check V$SORT_USAGE is see 20+ LOB_DATA or LOB_INDEX TEMPORARY entries that reside in PSAPTEMP.
When I check V$TEMPORYAR_LOBS is see entries indicating CACHE_LOBS.
I am not sure how to proceed further with determining what is the root cause and a resolution.
DateUsed (kb)
Used %
5/8 72,832,000 51 5/7 68,995,072 48 5/6 54,336,512 38 5/5 39,149,568 27 5/4 24,988,672 17 5/3 11,931,648 8 5/2 9,745,408 6 5/1 5,886,976 4 4/30 8,956,928 6 4/29 8,113,1525
4/28 6,899,712 4 4/27 6,014,976 4Thanks