To find oracle db temp tablespace used size which table is correct? V$TEMP_EXTENT_POOL or V$SORT_SEGMENT

Question

Can anyone help me in finding temp tablespace used size? Which query will return correct value from below?

SELECT t.tablespace_name,df.allocated_bytes as ALLOCATED_BYTES,decode(sign(df.allocated_bytes-NVL(f.used_bytes,0)),1,df.allocated_bytes - NVL(f.used_bytes,0),0) as FREE_BYTES,decode(sign(df.allocated_bytes-NVL(f.used_bytes,0)),1,NVL(f.used_bytes,0),df.allocated_bytes) as USED_BYTES,decode(sign(NVL(df.allocated_blocks,0)-NVL(f.used_blocks,0)),1,NVL(df.allocated_blocks,0)-NVL(f.used_blocks,0),0) FREEBLOCKS,NVL(df.allocated_blocks,0) ALLOCATEDBLOCKS FROM sys.dba_tablespaces t,(SELECT h.tablespace_name, ROUND(sum((ss.used_blocks*tt.block_size)),2) used_bytes, SUM(NVL(ss.used_blocks, 0)) used_blocks FROM V$TEMP_SPACE_HEADER h,***v$sort_segment*** ss,sys.dba_tablespaces tt where ss.tablespace_name(+)=h.tablespace_name and ss.tablespace_name(+)=tt.tablespace_name and ss.con_id = (select con_id from v$mystat where rownum=1) GROUP BY h.tablespace_name) f,(select tablespace_name,sum(decode(sign(maxbytes-bytes),1,maxbytes,bytes)) allocated_bytes,sum(blocks) allocated_blocks from dba_temp_files group by tablespace_name) df WHERE t.contents='TEMPORARY' and t.tablespace_name = f.tablespace_name(+) and t.tablespace_name=df.tablespace_name(+) order by t.tablespace_name

or

SELECT t.tablespace_name,df.allocated_bytes as ALLOCATED_BYTES,decode(sign(df.allocated_bytes-NVL(f.used_bytes,0)),1,df.allocated_bytes - NVL(f.used_bytes,0),0) as FREE_BYTES,decode(sign(df.allocated_bytes-NVL(f.used_bytes,0)),1,NVL(f.used_bytes,0),df.allocated_bytes) as USED_BYTES,decode(sign(NVL(df.allocated_blocks,0)-NVL(f.used_blocks,0)),1,NVL(df.allocated_blocks,0)-NVL(f.used_blocks,0),0) FREEBLOCKS,NVL(df.allocated_blocks,0) ALLOCATEDBLOCKS FROM sys.dba_tablespaces t,(SELECT h.tablespace_name, SUM(NVL(p.bytes_used, 0)) used_bytes, SUM(NVL(ss.used_blocks, 0)) used_blocks FROM V$TEMP_SPACE_HEADER h,***V$TEMP_EXTENT_POOL p***,v$sort_segment ss where p.tablespace_name(+)=h.tablespace_name and ss.tablespace_name(+)=h.tablespace_name GROUP BY h.tablespace_name) f,(select tablespace_name,sum(decode(sign(maxbytes-bytes),1,maxbytes,bytes)) allocated_bytes,sum(blocks) allocated_blocks from dba_temp_files group by tablespace_name) df WHERE t.contents='TEMPORARY' and t.tablespace_name = f.tablespace_name(+) and t.tablespace_name=df.tablespace_name(+) order by t.tablespace_name

Thanks!

You have posted two really long and badly formatted queries. If you want people to read through them you should edit them so they can be read and compared without scrolling. — APC, Mar 17 '20 at 12:06
Neither. They both return syntax errors. What's up with "from v$temp_space_header h,***v$sort_segment***". Are those asterisks to hightlight the difference between the two queries? If so, even after I remove those, I get syntax errors. ss.con_id - invalid identifier. I won't look for any more. Let's step back and ask (as we always should), "what is the _business problem_ you are trying to solve?" If we knew that, we might find that these queries are fundamentally useless, or that what you perceive to be a problem really isn't. — EdStevens, Mar 17 '20 at 13:13
Unrelated to your problem, but: even Oracle recommends to stop using the `(+)` operator and use a standard `left join` instead — , Mar 17 '20 at 16:35

score 0 · Answer 1 · answered Mar 17 '20 at 16:22

I use the following query against v$sort_segment to get current usage:

-- outer join version to get all TS's
SELECT d.name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts# (+)
and b.name like 'TEMP%'
GROUP BY B.name, C.block_size) D
WHERE A.tablespace_name (+) = D.name
GROUP by d.name, D.mb_total
;

V$sort_usage will show which sessions/sql are using TEMP. See the following example:

with sort as 
(
SELECT username, sqladdr, sqlhash, sql_id, tablespace, session_addr
       , sum(blocks) sum_blocks
FROM v$sort_usage 
GROUP BY username, sqladdr, sqlhash, sql_id, tablespace, session_addr
)
, temp as
(
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total
)
SELECT to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') sample_time
, sess.sql_id
, CASE WHEN elapsed_time > 2*86399*1000000
THEN '2 ' || to_char(to_date(round((elapsed_time-(2*86399*1000000))/decode(executions, 0, 1, executions)/1000000) ,'SSSSS'), 'HH24:MI:SS') 
WHEN elapsed_time > 86399*1000000
THEN '1 ' || to_char(to_date(round((elapsed_time-(86399*1000000))/decode(executions, 0, 1, executions)/1000000) ,'SSSSS'), 'HH24:MI:SS') 
WHEN elapsed_time <= 86399*1000000
THEN to_char(to_date(round(elapsed_time/decode(executions, 0, 1, executions)/1000000) ,'SSSSS'), 'HH24:MI:SS') 
END  as time_per_execution
, sum_blocks*dt.block_size/1024/1024 usage_mb, sort.tablespace
, temp.mb_used, temp.mb_free, temp.mb_total
, sort.username, sess.sid, sess.serial#
, p.spid, sess.osuser, sess.module, sess.machine, p.program
, vst.sql_text
FROM sort,
     v$sqlarea  vst,
     v$session  sess,
     v$process  p,
     dba_tablespaces  dt
     , temp
WHERE sess.sql_id = vst.sql_id (+) 
  AND sort.session_addr = sess.saddr (+)
  AND sess.paddr = p.addr (+)
  AND sort.tablespace = dt.tablespace_name (+)
  AND sort.tablespace = temp.tablespace
order by 4 desc
;

Have fun!!

To find oracle db temp tablespace used size which table is correct? V$TEMP_EXTENT_POOL or V$SORT_SEGMENT

1 Answers1