Oracle TEMP tablespace usage

temp space info
temp usage
Temporary tablespace usage
all about temp tablespace
temp space usage



SYS AS SYSDBA>!cat temp.sql

col tablespace_name format a20

spool tempsp.txt
select sysdate dtstamp,
s.tablespace_name,
d.tbspc_mb,
s.total_blocks*8192/1024/1024 temp_tot_mb,
s.used_blocks*8192/1024/1024 temp_used_mb,
s.free_blocks*8192/1024/1024 temp_free_mb
from v$sort_segment s,
(select tablespace_name,sum(bytes/1024/1024) tbspc_mb
from dba_data_files
group by tablespace_name
union
select tablespace_name,sum(bytes/1024/1024) tbspc_mb
from dba_temp_files
group by tablespace_name) d
where s.tablespace_name=d.tablespace_name;

col sid format a10
col username format a12
col tablespace format a15

select s.sid || ',' || s.serial# sid,
s.username,
u.tablespace,
a.sql_text,
round(((u.blocks*p.value)/1024/1024),2) size_mb
from v$sort_usage u,
v$session s,
v$sqlarea a,
v$parameter p
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and p.name = 'db_block_size'
and s.username != 'SYSTEM'
group by
s.sid || ',' || s.serial#,
s.username,
a.sql_text,
u.tablespace,
round(((u.blocks*p.value)/1024/1024),2);

or

compute sum of mb on report
compute sum of pct on report
col sid format a10 heading "Session ID"
col username format a10 heading "User Name"
col sql_text format a8 heading "SQL"
col tablespace format a10 heading "Temporary|TS Name"
col mb format 999,999,990 heading "Mbytes|Used"
col pct format 990.00 heading "% Avail|TS Spc"

select s.sid || ',' || s.serial# sid,
s.username,
u.tablespace,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)) sql_text,
u.blocks/128 mb,
((u.blocks/128)/(sum(f.blocks)/128))*100 pct,
s.sql_id
from v$sort_usage u,
v$session s,
v$sqlarea a,
dba_data_files f
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and f.tablespace_name = u.tablespace
group by
s.sid || ',' || s.serial#,
s.username,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)),
u.tablespace,
u.blocks/128,
s.sql_id




-----

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks from v$sort_segment) s,
(select sum(blocks) total_blocks from dba_temp_files) f;

spool off


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;


TABLESPACE MB_TOTAL MB_USED MB_FREE
-------------------- ---------- ---------- ----------
TEMP 13240 1 13239




col SID_SERIAL format a12
col PROGRAM format a15
col OSUSER format a10
col MODULE format a10
col SPID format a10
col USERNAME format a10
col SQL_ID format a14
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements,s.sql_id
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,P.program, TBS.block_size, T.tablespace,s.sql_id
ORDER BY sid_serial;

SID_SERIAL USERNAME OSUSER SPID MODULE PROGRAM MB_USED TABLESPACE STATEMENTS SQL_ID
------------ ---------- ---------- ---------- ---------- --------------- ---------- -------------------- ---------- --------------
138,3 PROD1 admin 28520 documentum oracle@co 1 TEMP 1
@dc ratst
tm-01tst (
TNS V1-V3)



SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status

FROM v$session a,v$sort_usage b

WHERE a.saddr = b.session_addr

ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;


TABLESPACE SEGFILE# SEGBLK# BLOCKS SID SERIAL# USERNAME OSUSER STATUS
-------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------------
TEMP 201 9 128 138 3 PROD1 admin INACTIVE




col USERNAME format a20
col TABLESPACE format a20

SELECT SE.USERNAME,
SE.SID,
se.serial#,
SU.EXTENTS,
(SU.BLOCKS * TO_NUMBER(RTRIM(P.VALUE)))/1024/1024 AS SPACE,
TABLESPACE,
SEGTYPE
FROM V$SORT_USAGE SU, V$PARAMETER P, V$SESSION SE
WHERE P.NAME = 'db_block_size'
AND SU.SESSION_ADDR = SE.SADDR
ORDER BY SE.USERNAME, SE.SID




set linesize 200

col user format a14

select USER,SESSION_NUM,sortu.TABLESPACE,sysdate,sql_text,sorts,sqla.sql_id

from v$sort_usage sortu,v$sqlarea sqla

where upper (tablespace) like 'TEMP%'

and sortu.SQLHASH=sqla.HASH_VALUE ;


USER SESSION_NUM TABLESPACE SYSDATE SQL_TEXT SORTS SQL_ID

-------------- ----------- -------------------- --------------- ---------------------------------------------------------------------------------------------------- ---------- --------------

SYS 3 TEMP 03-OCT-11 SELECT dm_dbalias_B.R_OBJECT_ID FROM prod1.DM_SYSOBJECT_S dm_dbalias_B WHERE (dm_dbalias_B.R_OBJ 0 5q7x0kvp3u19r

ECT_ID=:objectp AND dm_dbalias_B.I_VSTAMP=:versionp)


============

from web


Temporary tablespace usage


In database :

CREATE TABLE "AVAIL"."MONITOR_SORTSEG"
( "TABLESPACE_NAME" VARCHAR2(31),
"USED_EXTENTS" NUMBER,
"FREE_EXTENTS" NUMBER,
"ISSUE_TIME" DATE);

In OS : (monitor_temp.sh)

#!/bin/ksh
SCRIPT=1; export SCRIPT
. ~orfnprd/.profile
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<\EOF
set serveroutput on
begin
insert into monitor_sortseg
select tablespace_name,USED_EXTENTS,FREE_EXTENTS,sysdate
from v\$sort_segment where upper(tablespace_name)='TEMP' and free_extents<=500;
if(SQL%ROWCOUNT<>0) then
dbms_output.put_line('Start -------- Start Date:'||sysdate);
dbms_output.put_line('TEMP free extent is lower than 50 .... Finding SQL statement');
insert into monitor_sortuse select USER,SESSION_NUM,sortu.TABLESPACE,
sysdate,sql_text,sorts from v\$sort_usage sortu,v\$sqlarea sqla where upper
(tablespace)='TEMP' and sortu.SQLHASH=sqla.HASH_VALUE ;
commit;
dbms_output.put_line('End ----------End Date:'||sysdate);
end if;
end;
/
exit;
EOF


0,5,10,15,20,25,30,40,45,50,55 * * * * sample_temp.sh > sample_temp.log 2>&1


====





temp tablespace usage


COLUMN username HEADING "UserName" FORMAT a30
COLUMN osuser HEADING "OS User" FORMAT A10
COLUMN qcsid HEADING "QC" FORMAT 9999
COLUMN sid HEADING "SID" FORMAT 9999
COLUMN segtype HEADING "Segment|Type" FORMAT A10
COLUMN sql_id HEADING "SQL ID" FORMAT A15

CLEAR BREAKS
COMPUTE SUM OF sum_blocks ON qcsid
COMPUTE SUM OF sum_blocks ON osuser
COMPUTE SUM OF sum_blocks ON report
COMPUTE SUM OF sum_mb ON qcsid
COMPUTE SUM OF sum_mb ON osuser
COMPUTE SUM OF sum_mb ON report
BREAK ON username ON osuser ON qcsid ON report

SELECT s.username
, s.osuser
, ps.qcsid
, s.sid
, su.segtype
, su.sql_id
, SUM(su.blocks) sum_blocks
, ROUND(SUM(su.blocks) * dt.block_size / (1024 * 1024) ) sum_mb
FROM v$sort_usage su
, v$session s
, v$px_session ps
, dba_tablespaces dt
WHERE s.sid = ps.sid(+)
AND s.saddr = su.session_addr
AND s.serial# = su.session_num
AND su.tablespace = dt.tablespace_name
GROUP BY s.username
, s.osuser
, s.sid
, ps.qcsid
, su.contents
, su.segtype
, su.sqladdr
, su.sqlhash
, su.sql_id
, dt.block_size
ORDER BY s.username
, s.osuser
, ps.qcsid
, su.sqladdr
, s.sid
/



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;