When you see that there is intense use of temp in a running database, it is necessary to determine who is using a temp segment for which operation. You can use the following query for this. It is possible to query globally with $g instead of v$ in the query.
It is necessary to determine the temp usage when you need to do an operation about temp tablespace. For example, you cannot drop temp tablespace if anyone is using temp. If you run the drop command while the Temp is in use, it will not execute until the end of use. You can detect and close the session with the following query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
select srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks, a.sid, a.serial#, a.username, a.osuser, a.status from v$session a, v$sort_usage srt where a.saddr = srt.session_addr order by srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks; |
